Validate if a variable exists and then select or implement a case if

[dcortell]

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));
%end;
%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;
select
(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; 
quit;

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

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

Bests

[ballardw]

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

[Tom]

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.

[Pankp]

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.