BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Silbad
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

14 REPLIES 14
Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10

Hi Tom,

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

Thanks - Linlin

Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10

  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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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

jeeth79usa
Calcite | Level 5

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 Smiley Happy

%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.

jeeth79usa
Calcite | Level 5

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 Smiley Happy

Ron_MacroMaven
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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 ! 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 107022 views
  • 7 likes
  • 5 in conversation