How to know whether a variable exists in a dataset

[Silbad]

Hello everyone,

Could you tell me if there's a SAS function (or an autocall macro) to know whether a variable exists or not in a dataset ?

The point is that I don't want to use a Datasets procedure or I/O Functions, but instead use a function (or autocall macro) this way (assuming that varexist is an autocall macro) :

data class;

  set sashelp.class (keep = name

                            %if %varexist(sashelp.class, weight) eq 1 %then weight;

                            %if %varexist(sashelp.class, xxx) eq 1 %then xxx;

                     );

run;

Thanks for your help.

Sincerely yours,

Anthony

[Tom · Accepted solution]

Editor's Note: This is a popular topic.  The following SAS Note also shows a similar method for checking for the existence of a variable in a data set:

Sample 25082

In addition, here is a DATA Step example:

data test;
 input fruit $ count;
 datalines;
apple 12
banana 4
coconut 5
date 7
eggs 9
fig 5
;
run;

data _null_;
 dsid=open('test');
 check=varnum(dsid,'count');
 if check=0 then put 'Variable does not exist';
 else put 'Variable is located in column ' check +(_1) '.';
run;

Here is one you can use.  I have adjusted it to return 0 or 1 to match how you coded your program, but normally I just have it return the actual variable number. If you did that then you would change your code by eliminating the "eq 1" from your conditionals. SAS treats all non-zero values as TRUE.

%macro varexist
/*----------------------------------------------------------------------
Check for the existence of a specified variable.
----------------------------------------------------------------------*/
(ds        /* Data set name */
,var       /* Variable name */);

/*----------------------------------------------------------------------
Usage Notes:

%if %varexist(&data,NAME)
  %then %put input data set contains variable NAME;

The macro calls resolves to 0 when either the data set does not exist
or the variable is not in the specified data set.
----------------------------------------------------------------------*/

%local dsid rc ;

/*----------------------------------------------------------------------
Use SYSFUNC to execute OPEN, VARNUM, and CLOSE functions.
-----------------------------------------------------------------------*/

%let dsid = %sysfunc(open(&ds));

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

%mend varexist;

[Linlin]

Hi Tom,

When we use open function, do you have to use close function or it is optional?

Thanks - Linlin

[Tom]

Unless you are planning to immediately close SAS.  Try this little test.

data x;

y=1;

run;

%let dsid=%sysfunc(open(x));

data x;

y=2;

run;

%put %sysfunc(close(&dsid));

[Linlin]

  Thank you Tom!

So it should be fine without using close function as long as we don’t want to create same named dataset.

data x;
y=1;
run;
%let dsid=%sysfunc(open(x));

data new;
set x;
run;
proc print;run;

/*data x;
y=2;
run;
%put %sysfunc(close(&dsid)); */

[Tom]

If you do it thousands of time it could end up using up significant amounts of memory storing those active open file handles.

It also depends on the method you want to use the dataset in the future.  The dataset is open in read mode, so future read operations on the dataset are not impacted, but any operation that would lock or write to the dataset will not work.

[Linlin]

Thank you Tom! So it is a better practice to use open and close together. - Linlin

[jeeth79usa]

Hi Tom,

I have a similar scenario. I have a data set, and the some variables in it are

A1-A5

B1-B5

C1-C5

I would like to just check if these variables exist, and if its doesn't then read only the existing ones from the above mentioned vars.

I am trying to avoid the error, and this is causing the wrong results in my PDF. Would your example work in my case or I have to take different approach.

This is what I have so far

%macro varexist

/*----------------------------------------------------------------------

Check for the existence of a specified variable.

------------------------------------------------------------------------*/

(ds        /* Data set name */

,var       /* Variable name */

);

/*-----------------------------------------------------------------------

Usage Notes:

%if %varexist(&data,NAME)

%then %put input data set contains variable NAME;

The macro calls resolves to 0 when either the data set does not exist

or the variable is not in the specified data set.

----------------------------------------------------------------------*/

%local dsid rc ;

%*----------------------------------------------------------------------

Use SYSFUNC to execute OPEN, VARNUM, and CLOSE functions.

-----------------------------------------------------------------------;

%let dsid = %sysfunc(open(&ds));

%if (&dsid) %then %do;

  %if %sysfunc(varnum(&dsid,&var)) %then &var/100;

  %else 0 ;

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

%end;

%else 0;

%mend varexist;

%varexist(ds,A1-A5);

%varexist(ds,B1-B5);

%varexist(ds,C1-C5);

I was thinking to create macro variables for these vars using %let on the top.

Thanks in Advance.

Jeeth.

[Ron_MacroMaven]

see also:

http://www.sascommunity.org/wiki/Conditionally_Executing_Global_Statements

http://www.sascommunity.org/wiki/Macro_Assertions_for

[jeeth79usa]

Ron, Thanks a bunch for your reply. The second link seems to be suitable for my requirement. But, I need to check for the missing variables, and if the variable is missing, I am getting the error, and I still need to print appropriate results, and avoid the errors. I am not sure what exit macro does in the second example. I will apply this macro and see if it solves the purpose

[Ron_MacroMaven]

If I had the same problem as you originally stated

I would use Tom's macro.

Based on the way I program and my naming conventions

I suggest that there may be other information available to you

which you can use to provide a list of the variables in a data set for

-- in your example a keep statement --

some processing.

You can, for instance, use SQL to return a list of the variable in a data set into a macro variable:

http://www.sascommunity.org/wiki/Making_Lists

%let Libname = sashelp;

%let Memname = class;

PROC SQL;

   select Name

   into :Varlist separated by ' '

   from  Dictionary.Columns

   where  Libname eq "%upcase(&Libname.)"

     and  Memname eq "%upcase(&Memname.)"

     and  MemType eq 'DATA';

   quit;

%Put Varlist: &VarList.;

*

then use the index function to check for a name in the list

;

%let indexSex = %index(&VarList,Sex);

%let existSex = %sysfunc(ifc(%index(&VarList,Sex)

     ,%nrstr(1)

     ,%nrstr(0)

     ));

%Let Var = Gender;

%let exist&Var = %sysfunc(ifc(%index(&VarList,&Var)

     ,%nrstr(1)

     ,%nrstr(0)

     ));

%put _user_;

NOTE: sashelp.class var names are in Propcase.==Initial Caps

to ensure that this trick works

be sure to standardize the case to either lowcase or upcase

Personally I prefer lowcase.

Ron Fehd  macro maven

[jeeth79usa]

Ron,

Thanks for the reply. It seems to be a good trick. I am creating my variables. Once, I use this trick to check for the variables, I would like to use only the existing variables to avoid the errors in the next step. Please let me know if you can provide me a scenario for this.

Thanks in advance.

Jeeth.

[Ron_MacroMaven]

Compare to Tom's suggestion.

This code is lifted from Macro CallText

http://www.sascommunity.org/wiki/Macro_CallText

/*	name: ...\SAS-site\macros\listvars.sas

description: macro function: return list of vars in data set

purpose

: demo SCL open, varname, and close functions

******/

%MACRO listvars

	(Data	= sashelp.class
	,Testing = 0

)/ des = 'site: macro function: return list of vars in data set'

/**** ** store source /* */

;/* RJF2 2/27/2013 2:05:28 PM

****** NOTE: _C_*: avoid name collisions w/data set vars ***/

%local _C_Col _C_Dsid _C_Hex16 _C_Nobs _C_Nvars   _C_Rc

	_C_Row _C_Type _C_Name  _C_Num  _C_Testing _C_Text
	_C_Testing _C_TimeStart _C_TimeEnd;

%let _C_Testing= %eval(   &Testing

	or(	%sysfunc(getoption(MPRINT))  eq MPRINT
	and %sysfunc(getoption(SOURCE2)) eq SOURCE2));

%let _C_TimeStart = %sysfunc(datetime(),hex16.);

%**  description: assertions;

%**  purpose

: if fail then exit;

%if  not(%sysfunc(exist(&Data.))) %then %do;

	%put Err%str()or: &SysMacroname. exiting: not exist(&Data.);
	%return;
	%end;
%let _C_Dsid  = %sysfunc(open (&Data.	));

%let _C_Nobs  = %sysfunc(attrn(&_C_Dsid.,Nobs ));

%let _C_Nvars = %sysfunc(attrn(&_C_Dsid.,Nvars));

%if  not &_C_Nobs. or not &_C_Nvars. %then %do;

  %put Err%str()or: &SysMacroName. &Data. obs=&_C_Nobs. vars=&_C_Nvars.;

	%goto CloseExit;
	%end;

%else

%put note: &SysMacroname. reading &Data. obs=&_C_Nobs. vars=&_C_Nvars.;

%do _C_Col = 1 %to &_C_Nvars.;

	%sysfunc(varname (&_C_Dsid.,&_C_Col. ))
	%end;

%CloseExit: %let _C_Rc = %sysfunc(close(&_C_Dsid.));

%mend listvars;

compare with Tom's original solution for each variable.

Ron Fehd  SCL maven

%Let ListVars =%listvars(Data = sashelp.class);

%put ListVars:&ListVars.;

%Let ListVars =%listvars(Data = sashelp.heart);

%put ListVars:&ListVars.;

*error: not exits data;

%Let ListVars =%listvars(Data = sashelp.classX);

%put ListVars:&ListVars.;

[Linlin]

is the example below helpful:

%let dsn=sashelp.class;

%let var1=sex;

%let var2=abc;

data _null_;

   dsid=open("&dsn");

   n=ifc(varnum(dsid,"&var1"),"&var1",' ');

   m=ifc(varnum(dsid,"&var2"),"&var2",' ');

   call symputx('vv',n);

   call symputx('ww',m);

run;

%put &vv &ww;

proc print data=sashelp.class;

var age &vv &ww;

run;

[Silbad]

Your answers are both good, but Tom's answer is more useful for what I'm trying to do (i.e. use a macro instead of using data steps).

Thank you very much, Linlin and Tom ! 🙂