Category: SAS

SQL-statement to find lowest observation in dataset

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;

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;

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

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;

 

Microsoft Team Foundation Server (TFS) and SAS

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);

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

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. The ‘attr‘ option writes additional information about the variables in the input data sets and views. The ‘expandmacros‘ option expands macro invocations into separate tasks.

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

Be aware, that PROC SCAPROC is not able to get information from the execution of your SAS-program flow, if you are rsubmitting to other servers.

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. http://support.sas.com/resources/papers/proceedings17/1104-2017.pdf

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

Uniqueness in data

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;

 

SAS crashes when you connect with RDP

If your SAS-session crashes when you connect with RDP to a server the root-cause could be a check that SAS does for available printers.

To avoid these crashes it is possible to set a parameter in the config-file for SAS. The main config-file for SAS is called SASv9.cfg and in SAS 9.2 it’s usually located in C:\Program Files\SAS\SASFoundation\9.2\nls\en.

The parameter you have to add to the config-file is:
-noqueryports

Quote macrovariables e.g. for an IN SQL-statement

The code below lets you qoute the contant of a macrovariables e.g. to be used in an IN SQL-statement.

%let Variables = age height weight;
%let InStatement = %Str(%’)%sysfunc(Tranwrd(&Variables.,%Str( ),%Str(%’, %’)))%Str(%’);

The macrovariable InStatement will contain ‘age’,’height’,’weight’. And can then be used in an IN SQL-statement.