Macro To check if a variable exists in SAS dataset

0
15

Have you ever needed to know if a given variable exists is SAS data set? Below is a macro which will check for the existence of a variable in a dataset and will return a value of 0 if the variable does not exits and the column number if the variable exists.

The macro has 2 required parameters and 1 optional parameter.

  • DATA parameter accepts 2 level SAS dataset name Example. SASHELP.CARS
  • VAR is the variable name. Example Model
  • INFO is for variable attributes. Example FMT

The macro uses Variable functions in a data step and returns the variable info. I will return the below attributes of the variable when appropriate parameters are passed in the INFO parameter. If no value is passed, it returns the column position of the variable by default.

  • Len – To return the length of the variable.
  • fmt – to return Format of the variable
  • infmt – to return the informal of the variable.
  • label – to return Label of the variable
  • type – To return type of variable. (Character of Numeric).

Example:

If a variable exists
%put EXISTS: %varexist(sashelp.class,age);  – Returns 3

If a variable does not exist
%put EXISTS: %varexist(work.test,doesnotexist); – Returns 0 ;

If a Dataset does not exist
%put EXISTS: %varexist(doesnotexist,doesnotexist); – Returns 0 ;

Variable attribute usage:

%put LENGTH: %varexist(sashelp.class,age,len);
%put FORMAT: %varexist(sashelp.class,age,fmt);
%put INFORMAT: %varexist(sashelp.class,age,infmt);
%put LABEL: %varexist(sashelp.class,age,label);
%put TYPE: %varexist(sashelp.class,age,type);


options symbolgen mprint mlogic;
%macro varexist(data,var,info);
%local macro parmerr dsis rc varnum;
%let macro = &sysmacroname;
%let dsid = %sysfunc(open(&data));

%if (&dsid) %then %do;
   %let varnum = %sysfunc(varnum(&dsid,&var));

   %if (&varnum) %then %do;
      %if (%length(&info)) %then %do;
         %if (&info eq NUM) %then %do;
&varnum
         %end;
         %else %do;
%sysfunc(var&info(&dsid,&varnum))
         %end;
      %end;
      %else %do;
&varnum
      %end;
   %end;

   %else 0;

   %let rc = %sysfunc(close(&dsid));
%end;

%else 0;
%mend;

/******* END OF FILE *******/
%put EXISTS:   %varexist(sashelp.class,age); 
%put VARNUM:   %varexist(sashelp.class,age,num);
%put LENGTH:   %varexist(sashelp.class,age,len);
%put FORMAT:   %varexist(sashelp.air,date,fmt);
%put INFORMAT: %varexist(sashelp.class,age,infmt);
%put LABEL:    %varexist(sashelp.class,age,label);
%put TYPE:     %varexist(sashelp.class,age,type);

SAS Log:

%put EXISTS: %varexist(sashelp.class,age);
EXISTS: 3
%put VARNUM: %varexist(sashelp.class,age,num);
VARNUM: 0
%put LENGTH: %varexist(sashelp.class,age,len);
LENGTH: 8
%put FORMAT: %varexist(sashelp.air,date,fmt);
FORMAT: MONYY.
%put INFORMAT: %varexist(sashelp.class,age,infmt);
INFORMAT:
%put LABEL: %varexist(sashelp.class,age,label);
LABEL:
%put TYPE: %varexist(sashelp.class,age,type);
TYPE: N

Download this program

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.