Conditionally Create a Variable if it Does Not Exist

[BCNAV]

We have data going back many years. As time has moved forward, one variable has been removed as it is no longer being used for billing purposes. This variable is called nbday. I am creating a reporting tool that needs to go back in time to when this variable existed. Each dataset is based on a fiscal year basis (not made by me), hence nbday may be in it or it may not be.

So I need to take each individual fiscal year file (I never know which year I will need) and check to see the existence of nbday. If it does not exist, create it and give it a value of 1. If it does exist, keep it at the value it has.  Something like:

data egtask.test;

     set fiscal2019;

     if nbday does not exist, then create nbday and set equal to 1;

     if nbday exists, then keep it;

run;

Or maybe sql?  case when nbday does not exist then nbday=1 else nbday?

No sure this is possible or not in a datastep.

thanks

-Bill

[PaigeMiller]

You can check to see if a variable exists using PROC CONTENTS or the DICTIONARY tables

proc sql noprint;
     select count(*) into :exists2019 from dictionary.columns
         where upcase(libname)="WORK" and upcase(memname)="FISCAL2019"
         and ucpase(name)='NBDAY';
quit;

This creates a macro variable &EXISTS2019 that is 0 if the variable does not exist and 1 if the variable does exist.

Then in your data step:

%if not &exists2019 %then nbday=1%str(;);

[BCNAV]

So I am using:

proc sql noprint;
     select count(*) into :exists from dictionary.columns
         where upcase(libname)="EGTASK" and upcase(memname)="DAILYFY_IND_CURR_T1"
         and upcase(name)='NBDAY';
quit;

data egtask.lollypop;
	set EGTASK.DAILYFY_IND_CURR_T1;
		%if not &exists %then nbday=1%str(;);
run;

I am getting the error: ERROR: The %IF statement is not valid in open code. I could put a macro around it.

Versions:

EG 7.15 HF8 (7.100.5.6214)

SAS 9.4 TS Level 1M3

Thx

[PaigeMiller · Accepted solution]

Ah, my mistake, I forget that using %IF in open code is a new feature with SAS 9.4 Level 1M5.

You can turn the whole thing into a macro, and then it will work.

%macro dothis;
proc sql noprint;
     select count(*) into :exists from dictionary.columns
         where upcase(libname)="EGTASK" and upcase(memname)="DAILYFY_IND_CURR_T1"
         and upcase(name)='NBDAY';
quit;

data egtask.lollypop;
	set EGTASK.DAILYFY_IND_CURR_T1;
		%if not &exists %then nbday=1%str(;);
run;
%mend;
%dothis

[Astounding]

If the variable does exist, would it always be populated?  If the existing variable never has a missing value, the solution is easy:

data egtask.test;
     set fiscal2019;
     if nbday = . then nbday = 1;
run;