Month: September 2013

Get SQL recipe for a dataset in SAS

The code below will make a file class.sql containing the SQL-code for creating the dataset sashelp.class

ods tagsets.sql file="class.sql";
 proc print data=sashelp.class ;
 run;
ods _all_ close;

The file will look something like this

Create table CLASS
(Name varchar(7), Sex varchar(1), Age float, Height float, Weight float);
Insert into CLASS(Name, Sex, Age, Height, Weight)
Values (‘Alfred’, ‘M’, 14, 69.0, 112.5);
Insert into CLASS(Name, Sex, Age, Height, Weight)
Values (‘Alice’, ‘F’, 13, 56.5, 84.0…

Making an empty dataset in SAS

The code below shows you how to make an empty dataset in SAS.
If you omit the if-sentence  and below, then you will get an empty row in the dataset.

data StatusTable;
 length Dataset $100 Message $150 Status $10;
 call missing(Dataset, Message, Status);
 if _N_ = 0 then output;
 stop;
run;

This can also be done a bit easier in SQL.

proc sql noprint;
create table Dataset
(
Dataset char 100,
Message char 150,
Status char 10
);
quit;

Comparing datasets in SAS

The code below compares two datasets. It merges them together and makes three datasets. One dataset contains identical observations from the two datasets. The second dataset contains observations only found in one dataset. And the third dataset contains observations only found in the other dataset.

data InBoth InOne InTwo;
merge One (in=a) Two (in=b);
by <variables>;
if a and b then
 output InBoth;
if a and not b then
 output InOne;
if b and not a then
 output InTwo;
run;

 

Return value from SAS macro

The code below shows how to return a value from a SAS macro.

/*
Returns 0 if a specific variable is not found in a dataset and 1 if it is found.
*/
%macro VarExist(_Dataset=, _Variable=);
  %local dsid rc ;
  %let dsid = %sysfunc(open(&_Dataset));

  %if (&dsid) %then %do;
     %if %sysfunc(varnum(&dsid,&_Variable)) %then 1;
     %else 0 ;
     %let rc = %sysfunc(close(&dsid));
  %end;
  %else 0;
%mend VarExist;

/*
Go to do something if the dataset sashelp.class contains the variable
age - and it does.
*/
%if %VarExist(_Dataset=sashelp.class, _Variable=age) %then
%do;
/* Something */
%end;

It is also possible to return a value from a macro using the code below. This only works for simple macros.

%macro add(no1=, no2=);
 %let result = %eval(&no1 + &no2);

 &Result
%mend;

%let sum = %add(no1=2, no2=2);