BookmarkSubscribeRSS Feed
Pyrite | Level 9

Hi All dears


I'm in the need to check if within a dataset a specif column exists, and depending on the existance, selecting the variable or execute a case if step.


The check must be used within a proc sql



I had the idea of doing the checks in  Macros ( first macro to check the existance, second macro including the two action depending on the presence or not of the variable), and then utilize the same macro in the final  proc sql to define a variable related to the specifc variable/month


the idea would be


1. Macro - checking or not the existance of the column

LIBNAME EC1 "/intl/iimis4/prod/cgf/emea/spain";

%let bom=200;

%macro varexist
Check for the existence of a specified variable.
(ds   =   ec1.cust_seg 
,var  =  cust_seg_mth&bom
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));
%else 0;
%mend varexist;

2. Macro - dependign on the existance or not select one of the two option (1 select the variable / 2 execute the case if)

%macro test; 
   %if %varexist = 1 %then %do; 
      proc sql ;  
         select cust_seg_mth&eom from ec1.cust_seg; quit;  
   %end; %else %do; 
    proc sql;
(case when (intck('month',t2.cust_estab_dt, '31aug2016'd )>=12 and cust_rel_ind&eom=1) then "Organic"
 when (intck('month',t2.cust_estab_dt, '31aug2016'd)>=12 and cust_rel_ind&eom=0) then "Attrition"
when intck('month',t2.cust_estab_dt, '31aug2016'd)<=11 then "Acquired" end) 
 from ec1.cust_seg; 

%mend test; 

3. Recall the macro in the fianl proc sql;

proc sql;
create table histcust as 
select t1.acct11, t2.cust_estab_dt,
cust_seg_mth181 as jan2015,
cust_seg_mth182 as feb2015,
cust_seg_mth183 as mar2015,
cust_seg_mth184 as apr2015,
cust_seg_mth185 as may2015,
cust_seg_mth186 as jun2015,
cust_seg_mth187 as jul2015,
cust_seg_mth188 as aug2015,
cust_seg_mth189 as sep2015,
cust_seg_mth190 as oct2015,
cust_seg_mth191 as nov2015,
cust_seg_mth192 as dec2015,
cust_seg_mth193 as jan2016,
cust_seg_mth194 as feb2016,
cust_seg_mth195 as mar2016,
cust_seg_mth196 as apr2016,
cust_seg_mth197 as may2016,
cust_seg_mth198 as jun2016,
cust_seg_mth199 as jul2016,
%test  as aug2016

from  ec1.acct_attr t1 left join ec1.cust_seg t2 on t1.acct11=t2.acct11
left join ec1.cust_attr t3 on t1.amex_cust_id=t3.amex_cust_id

order by t1.acct11, t1.amex_cust_id


Now, I'm not a macro user so I have tried something but getting as natural error


I suppose the first macro give a 1 or 0 depending on the variable presence but not sure on the second one


The second macro should be supposed to give a specific value (the corresponding variable record or the result of the case if), related to each ID (acct11) of each record derived in the final proc sql


Anyone could help ??


Thanks in advance for the precious support



Super User

When you get an error message please post the appropriate section of the Log.


I suspect you have at least few things going on.

First you attempt to use the macro with this code:

 %if %varexist = 1 %then %do;

Your default value for VAR involved a macro variable in defining the value for the VAR parameter (a not optimal approach) so the value of &BOM at time of compiling is the default and may not match the value you expect when not actually using the macro.


Your sql code to execute when the varexist is true has

    select cust_seg_mth&eom 

but the variable tested for was cust_seg_mth&bom 


For the results you should provide a few records of the input variables of interest and what you expect for the output as it is not obvious what you want.


Second is likely to be Scope

Super User Tom
Super User

In your second macro you didn't tell the %VAREXIST macro what variable to check for. So the result will always be 0.


The macro calls resolves to 0 when either the data set does not exist
or the variable is not in the specified data set.
Fluorite | Level 6

First of all, I see the problem is you are trying to execute Proc Sql within from %test macro within proc sql in following steps.

You need to only consider select statement.Please check first.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


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
  • 3 replies
  • 4 in conversation