topic whats wrong with this varexist function ? in SAS Enterprise Guide

whats wrong with this varexist function ?

alepage — Mon, 16 Sep 2024 19:27:35 GMT

Hello,

I am trying to test the varexist function but I don't get the expected result.

Can someone Help me with that issue

%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;
%mend varexist;
%let fname=class;
%let var=Name;

%macro test;

data test;
set sashelp.class;
%if %varexist(&fname.,&var.) eq 1 %then rename Name=Name1;
run;

%mend test;
%test;

Re: whats wrong with this varexist function ?

PaigeMiller — Mon, 16 Sep 2024 19:40:49 GMT

You don't have a data set named CLASS.

Also, you are missing a semi-colon, it should be

%if %varexist(&fname.,&var.) eq 1 %then rename Name=Name1%str(;);

Re: whats wrong with this varexist function ?

alepage — Mon, 16 Sep 2024 20:14:46 GMT

how will you do that in a proc sql ?

						proc sql;
                					create table table_info as
									%if %varexist(a,'police') eq 1 %then rename police=agreement_nbr%str(;);
                					select distinct a.agreement_nbr as agreement_nbr length=20, 
							      	a.lob as lob length=4,
                         			a.month as month length=3,
							      	a,year as year length=4,
                         			a.filename as filename length=25, 
                         			a.path as libname length=100,
									a.policy_expiry_date length=8,
									b.*
                		from work.&fname. as a
						inner join lookup as b
						on(a.agreement_nbr=b.agreement_nbr_dr and a.province_cd=b.province and a.company_cd=b.legacy_underwriting_company);
						
                		quit;

Please note that is some dataset we have the variable police that need to be rename Agreement_nbr and in other datasets we already have agreement_nbr.

Re: whats wrong with this varexist function ?

Tom — Mon, 16 Sep 2024 20:15:29 GMT

You are missing an ELSE branch for when the dataset does not exist (or cannot be opened).

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

Let's try it out:

34   %put Does NAME exist in SASHELP.CLASS? %varexist(var=NAME,ds=SASHELP.CLASS) ;
Does NAME exist in SASHELP.CLASS? 1
35   data class;
36     set sashelp.class;
37   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


38   %put Does NAME exist in CLASS? %varexist(class,name) ;
Does NAME exist in CLASS? 1
39   proc delete data=class; run;

NOTE: Deleting WORK.CLASS (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


40   %put Does NAME exist in CLASS? %varexist(class,name) ;
Does NAME exist in CLASS? 0

For the full version look here: https://github.com/sasutils/macros/blob/master/varexist.sas

Re: whats wrong with this varexist function ?

PaigeMiller — Mon, 16 Sep 2024 21:21:41 GMT

@alepage wrote:

how will you do that in a proc sql ?

I didn't give an answer for SQL because your question clearly is using a DATA step.

Re: whats wrong with this varexist function ?

Tom — Tue, 17 Sep 2024 02:07:14 GMT

I really doubt that you have a variable named

'police'

First you would have to have set the VALIDVARNAME option to ANY to allow SAS to even use a variable name with quotes in it. Second you would need to use a name literal like "'police'"n in the SAS code to reference it.

If the goal is to use one of two different names in the SQL code I find it much easier to first create a macro variable with the correct name to use, rather than trying to insert %IF and other macro logic into the middle of actual SAS statements.

Also the macro code is easier to type and easier to read if you just treat the result of %VAREXIST() call as a BOOLEAN value, instead of thinking of it as having numeric values that you need to test for equality to something.

%if %varexist(&fname,police) %then %let varname=police;
%else %if %varexist(&fname,agreement_nbr) %then %let varname=agreement_nbr;


proc sql;
create table table_info as
  select 
    a.&varname as agreement_nbr length=20
  , a.lob as lob length=4
  , a.month as month length=3
  , a.year as year length=4
  , a.filename as filename length=25
  , a.path as libname length=100
  , a.policy_expiry_date length=8
  , b.*
  from &fname. as a
  inner join lookup as b
    on a.&varname=b.agreement_nbr_dr
    and a.province_cd=b.province
    and a.company_cd=b.legacy_underwriting_company
;
quit;

Do you really have YEAR stored in a character variable? If it is a numeric variable why would you bother telling SAS to store only 4 of the 8 bytes needed to represent a number? You normally never need to tell SAS to use a storage length other than 8 for numeric variables.

PS Your TAB key seems to be messing up. You had random tabs inserted into your code which makes the code appear very jagged on the left. I find it works much better if you change the SAS editor settings to replace tab characters with actual spaces. That way it is much harder to make such a mess.