Using MSSQL TEMPDB in SAS

Posted on August 30, 2019 by Root

You can use the TEMPDB in Microsoft SQL-server through SAS by creating a ODBC-libname – like the libname below.

libname TMPLIB ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<SERVERNAME>; DATABASE=TEMPDB; TRUSTED_CONNECTION=yes" schema=DBO CONNECTION=SHARED;

1	libname TMPLIB ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<SERVERNAME>; DATABASE=TEMPDB; TRUSTED_CONNECTION=yes" schema=DBO CONNECTION=SHARED;

It’s important to provide the option CONNECTION=SHARED or else it will not work.

Through the libname it’s now possible to write and read from TEMPDB. The dataset has to have this syntax ‘#<DATASETNAME>’n e.g. like below ‘#temp’n

data tmplib.'#temp'n;
set sashelp.class;
run;

data tmplib.'#temp'n;

set sashelp.class;

run;

Be aware, that you are not able to view this new table through the Display Manager in SAS. When Microsoft SQL-server names the table, it makes the table name longer than SAS is able to display in the Display Manager.
You’re able to verify that the table do exist through SSMS (SQL Server Management Studio), or you can verify it’s existence by reading it back to SAS by using the code below.

proc sql;
create table temp as
select *
from tmplib.'#temp'n
;
quit;

proc sql;

create table temp as

select *

from tmplib.'#temp'n

;

quit;

If you want to use MSSQL-server temp-tables in Pass-Through SQL in SAS, then you need to use the libname option dbmstemp=yes.
Using this option will make it possible to execute the code below and force SAS to use the MSSQL-server to process the SQL-code in the pass-through SQL. If the option dbmstemp=yes is not used, then SAS will pull the data from the MSSQL-server back to be executed locally on the SAS-installation. It works with the below driver.

libname tmplib odbc noprompt="driver=odbc driver 11 for sql server; server=<servername>; database=tempdb; trusted_connection=yes" schema=dbo connection=shared dbmstemp=yes;

data tmplib.<SASTEMP-tablename>;
set <tablename>;
run;

proc sql noprint;
connect to odbc (noprompt="driver=odbc driver 11 for sql server; server=<servername>; trusted_connection=yes");
create table <tablename> as
select * from connection to odbc
(
select a.*
from <tablename> a
inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable>
);
disconnect from odbc;
quit;

libname tmplib odbc noprompt="driver=odbc driver 11 for sql server; server=<servername>; database=tempdb; trusted_connection=yes" schema=dbo connection=shared dbmstemp=yes;

data tmplib.<SASTEMP-tablename>;

set <tablename>;

run;

proc sql noprint;

connect to odbc (noprompt="driver=odbc driver 11 for sql server; server=<servername>; trusted_connection=yes");

create table <tablename> as

select * from connection to odbc

(

select a.*

from <tablename> a

inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable>

);

disconnect from odbc;

quit;

Further information can be found in this link: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/
default/viewer.htm#a002677192.htm

Extending the Display Manager in SAS

Posted on August 30, 2019 by Root

The commands below can be used to expand the Display Manager in SAS.

Command	Description
vt &syslast.;	Add to keys, e.g. F5. This opens a Viewtable with the latest run dataset/view.
next viewtable:; end;	Add to keys, e.g. F9. Then F9 closes the last used table (that is open). Can close all views with multiple F9’s.
odsresults; select all; clear; wpgm;	Add to keys, e.g. SHIFT F1. This key bind clears all SAS “results” and returns to the program editor.
log; clear; wpgm;	Add to keys, e.g. F4. Clears log without having to highlight the log window. Returns to the program editor.

Quoted SQL IN-statement in SAS

Posted on November 24, 2018 by Root

Below is code that produces a quoted string from a column in a dataset. This can be used in an IN-statement in SQL.

proc sql;
      select      quote(Compress(name)) into :SQLMetaSrc separated by ','
      from datasets
      ;
quit;

proc sql;

select quote(Compress(name)) into :SQLMetaSrc separated by ','

from datasets

;

quit;

SAS “behind the scenes”

Posted on August 12, 2018 by Root

Below is a description of very useful options in SAS, if you want a look “behind the scenes” and see what SAS actually does when processing data.

options fullstimer sastrace=(,,,d) sastraceloc=saslog mprint source2 nostsuffix;

1	options fullstimer sastrace=(,,,d) sastraceloc=saslog mprint source2 nostsuffix;

fullstimer	The SAS System provides the FULLSTIMER option to collect performance statistics on each SAS step, and for the job as a whole and place them in the SAS log. It is important to note that the FULLSTIMER measures only give you a snapshot view of performance at the step and job level.
sastrace=(,,,d)	Generates trace information from a DBMS engine. ‘,,,d’ specifies that all SQL statements that are sent to the DBMS are sent to the log. Here are the applicable statements: SELECT DELETE CREATE SYSTEM CATALOG DROP COMMIT INSERT ROLLBACK UPDATE For engines that do not generate SQL statements, API calls and all parameters are sent to the log.
sastraceloc=saslog	Prints SASTRACE information to a specified location. In this case the log in SAS.
mprint	Specifies whether SAS statements generated by macro execution are traced for debugging.
source2	Specifies whether SAS writes secondary source statements from included files to the SAS log. SOURCE2 specifies to write to the SAS log secondary source statements from files that have been included by %INCLUDE statements.
nostsuffix	The NOSTSUFFIX system option suppresses printing or display of trailing SASTRACE information and makes the SASTRACE log easier to read.

Reset Display Manager SAS

Posted on June 24, 2018 by Root

It’s possible to insert the command below under keys in the Display Manager (DM) in SAS. The command will reset the line number in the log, clear the log and jump back to the editor.

gsubmit 'resetline';log;clear;wpgm;

1	gsubmit 'resetline';log;clear;wpgm;

SQL-statement to find lowest observation in dataset

Posted on May 10, 2018 by Root

The SAS SQL-statement below will create a dataset containing all information for the observation with the lowest age in the dataset SASHELP.CLASS.

proc sql;
     create table CLASS as
           select     *
           from SASHELP.CLASS
           group by AGE
           having AGE=min(AGE)
     ;
quit;

proc sql;

create table CLASS as

select *

from SASHELP.CLASS

group by AGE

having AGE=min(AGE)

;

quit;

The syntax for the SAS SQL-statement is showed below.

proc sql;
     create table <DESTINATION TABLE> as
           select     *
           from <SOURCE TABLE>
           group by <COLUMN TO SEARCH>
           having <COLUMN TO SEARCH>=<FUNCTION>(<COLUMN TO SEARCH>)
     ;
quit;

proc sql;

create table <DESTINATION TABLE> as

select *

from <SOURCE TABLE>

group by <COLUMN TO SEARCH>

having <COLUMN TO SEARCH>=<FUNCTION>(<COLUMN TO SEARCH>)

;

quit;

As you see the <FUNCTION> doesn’t have to be min (minimum), it can be any function working on the type of <COLUMN TO SEARCH> – numeric or char.

Info about SAS-datasets in the WORK-library

Posted on April 25, 2018 by Root

In SAS Enterprise Guide it is not very easy to see the size and number of observations in datasets in the WORK-library.

The macro below looks in the DICTIONARY.TABLES and gets these info for the WORK-library. Be aware that it will not work for views, because it’s not doing and actual count of the SAS-datasets.

/********************************************************************************
Author        : 
Creation date : 
Description   : Gets info about datasets in the WORK-library.
Example       : %countwork(print);
*********************************************************************************
Input
-----
&print        : If not empty it will do a PROC PRINT of the dataset WORKDS created
                by the macro.
*********************************************************************************
Output
------
WORKDS        : Contains information about the datasets in the WORK-library.
********************************************************************************/
%macro CountWork(print);
          proc sql;
                   create table workds as
                             select    libname
                                                , memname
                                                , typemem
                                                , nobs format=commax10.0
                                                , filesize format=sizekmg.
                                                , nvar
                             from dictionary.tables
                             where libname eq 'WORK'
                             order by nobs
                   ;
          quit;
 
          %if &print. ne %then
          %do;
                   proc print data=workds;
                   run;
          %end;
%mend;

/********************************************************************************

Author :

Creation date :

Description : Gets info about datasets in the WORK-library.

Example : %countwork(print);

*********************************************************************************

Input

-----

&print : If not empty it will do a PROC PRINT of the dataset WORKDS created

by the macro.

*********************************************************************************

Output

------

WORKDS : Contains information about the datasets in the WORK-library.

********************************************************************************/

%macro CountWork(print);

proc sql;

create table workds as

select libname

, memname

, typemem

, nobs format=commax10.0

, filesize format=sizekmg.

, nvar

from dictionary.tables

where libname eq 'WORK'

order by nobs

;

quit;

%if &print. ne %then

%do;

proc print data=workds;

run;

%end;

%mend;

Microsoft Team Foundation Server (TFS) and SAS

Posted on April 9, 2018 by Root

Below is shown how you can extract the current Microsoft Team Foundation Server (TFS) revision number for a given file into SAS and use it in your SAS program.

Be aware that Visual Studio 13 is used in the example below. It is uncertain if newer versions of Visual Studio will work.

%macro TFSRev(filename);
    %let tfsver1=;
    %let tfsver2=;
    %let tfsver3=;
 
    /* The default path for the TF program tf.exe with Visual Studio 13. */
    %let TFRev=C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\tf.exe;
 
    filename tfver pipe "call ""&TFRev"" hist ""&filename"" ";
 
    data _null_;
        infile tfver;
        input;
        /* The output is in three different lines. */
        put _n_ _infile_;
        /* Puts the three different output lines in three diffent macrovariables called tfsver1, tfsver2 and tfsver3. */
        call symput('tfsver' !! put(_n_,1.), _infile_);
    run;
 
    /* Suppress output from SAS. */
    options nosource;
    %put Extracting TFS information from: &filename;
    %put;
    %put &tfsver1;
    %put &tfsver2;
    %put &tfsver3;
    option source;
%mend;

%TFSRev(C:\TEMP\sasprogram.sas);

%macro TFSRev(filename);

%let tfsver1=;

%let tfsver2=;

%let tfsver3=;

/* The default path for the TF program tf.exe with Visual Studio 13. */

%let TFRev=C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\tf.exe;

filename tfver pipe "call ""&TFRev"" hist ""&filename"" ";

data _null_;

infile tfver;

input;

/* The output is in three different lines. */

put _n_ _infile_;

/* Puts the three different output lines in three diffent macrovariables called tfsver1, tfsver2 and tfsver3. */

call symput('tfsver' !! put(_n_,1.), _infile_);

run;

/* Suppress output from SAS. */

options nosource;

%put Extracting TFS information from: &filename;

%put;

%put &tfsver1;

%put &tfsver2;

%put &tfsver3;

option source;

%mend;

%TFSRev(C:\TEMP\sasprogram.sas);

You can now use the three diffent macrovariables called tfsver1, tfsver2 and tfsver3 in your program.

Microsoft Visual Studio Team Foundation Server 2013 Power Tools might be needed.
https://marketplace.visualstudio.com/items?itemName=TFSPowerToolsTeam.MicrosoftVisualStudioTeamFoundationServer2013Power

You might also want to have a look at this blogpost regarding SVN.

Automatically analyzing and documenting SAS-code

Posted on March 28, 2018 by Root

In SAS Enterprise Guide and SAS-DI you have the possibility to analyze the code for a SAS-program.

The picture below shows the Analyze Program option in SAS Enterprise Guide.

Analyzing the code should result in a conversion of the SAS-code to a SAS Enterprise Guide flow or SAS-DI flow. But none of these code analyzers are very good. Depending on the complexity of the SAS-code you put into the analyzer, they will leave you with a more or less successful conversion. And more times than not they will fail at doing the job.

But SAS comes with a procedure PROC SCAPROC that does a really good job at analyzing and documenting SAS-code.

Below is an example.

proc scaproc;
record '<PATH>' attr expandmacros;
run;
 
proc scaproc;
write;
run;

proc scaproc;

record '<PATH>' attr expandmacros;

run;

proc scaproc;

write;

run;

The links below gives you further descriptions of PROC SCAPROC and its options. There’s also a guide on how to do a graphical presentation of the result fra PROC SCAPROC.

Overview of the SCAPROC Procedure
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199745.htm

Program for parsing the output from PROC SCAPROC to create a data set with inputs and outputs.
http://support.sas.com/kb/58/047.html

Innovative Performance Improvements Through Automated Flowcharts In SAS
http://support.sas.com/resources/papers/proceedings16/11580-2016.pdf

Automatically create diagrams showing the structure and performance of your SAS code
http://support.sas.com/resources/papers/proceedings17/1104-2017.pdf

Uniqueness in data

Posted on March 8, 2018 by Root

The SAS-macro below will tell you if a variable in a dataset is unique.

/********************************************************************************
Author        : 
Creation date : ddmmmyyy
Description   : Gets info about uniqness in a SAS-dataset.
Example       : %uniq(sashelp.class, name, print)
*********************************************************************************
Input
-----
&datset   : The dataset to test.
&variable : The variable to test for uniqueness.
&print    : If the output/result should be shown in a PROC PRINT.
*********************************************************************************
Output
------
freq_result          : Dataset sorted with doublets as first rows.
freq_result_doublets : Data containing only the doublets.
********************************************************************************/
%macro uniq(dataset, variable, print);
	proc freq data=&dataset.;
		tables &variable / noprint out=freq_result;
	run;

	proc sort data=freq_result;
		by descending count;
	run;

	data freq_result_doublets;
		set freq_result;
		where count gt 1;
	run;

	proc sql noprint;
		select count(*) into :doublets
		from freq_result_doublets
		where count gt 1
		;
	quit;

	%put --------------------------------------------------------------------------------------------;
	%put NUMBER OF DOUBLETS IN [%upcase(&dataset.)] FOR VARIABLE [%upcase(&variable.)]: &doublets.;
	%put --------------------------------------------------------------------------------------------;

	%if &doublets. eq 0 %then
	%do;
		%put !!!! NO DOUBLETS !!!;
	%put --------------------------------------------------------------------------------------------;
	%end;

        %if &print. ne %then
        %do;
             proc print data=freq_result;
             run;
        %end;
%mend;

/********************************************************************************

Author :

Creation date : ddmmmyyy

Description : Gets info about uniqness in a SAS-dataset.

Example : %uniq(sashelp.class, name, print)

*********************************************************************************

Input

-----

&datset : The dataset to test.

&variable : The variable to test for uniqueness.

&print : If the output/result should be shown in a PROC PRINT.

*********************************************************************************

Output

------

freq_result : Dataset sorted with doublets as first rows.

freq_result_doublets : Data containing only the doublets.

********************************************************************************/

%macro uniq(dataset, variable, print);

proc freq data=&dataset.;

tables &variable / noprint out=freq_result;

run;

proc sort data=freq_result;

by descending count;

run;

data freq_result_doublets;

set freq_result;

where count gt 1;

run;

proc sql noprint;

select count(*) into :doublets

from freq_result_doublets

where count gt 1

;

quit;

%put --------------------------------------------------------------------------------------------;

%put NUMBER OF DOUBLETS IN [%upcase(&dataset.)] FOR VARIABLE [%upcase(&variable.)]: &doublets.;

%put --------------------------------------------------------------------------------------------;

%if &doublets. eq 0 %then

%do;

%put !!!! NO DOUBLETS !!!;

%put --------------------------------------------------------------------------------------------;

%end;

%if &print. ne %then

%do;

proc print data=freq_result;

run;

%end;

%mend;

Ryslander.com

implementation, consolidation and innovation

Category Archives: SAS

Using MSSQL TEMPDB in SAS

Extending the Display Manager in SAS

Quoted SQL IN-statement in SAS

SAS “behind the scenes”

Reset Display Manager SAS

SQL-statement to find lowest observation in dataset

Info about SAS-datasets in the WORK-library

Microsoft Team Foundation Server (TFS) and SAS

Automatically analyzing and documenting SAS-code

Uniqueness in data