Creating a universal macro to derive EPOCH

[Dregerator]

Hi,

I'm having some issues trying to write the syntax for this macro. Basically, what I want it to do is to calculate _date variable if one or the other variable exists. For example, certain datasets have the --DTC variable and certain datasets have --STDTC variable. I want the code to calculate _date if the dataset has --DTC variable, but if the dataset doesn't have the --DTC variable then the _date variable is calculated using --STDTC variable. Currently, it's failing because of the first condition where I have set &dsn.dtc is causing _date to not be calculated because the dataset I have does not have DTC but instead has --STDTC. 

%macro util_generate_epoch(dsn= );
data ec;
set c373_un.ec(keep=usubjid ecstdtc);
ECSTD=substr(ecstdtc,1,10);
_ECSTD=input(ecstd,yymmdd10.);
format _ecstd yymmdd10.;
drop ecstdtc ECSTD;
run;

proc sort data=ec;
by usubjid;
run;

proc sort data=&dsn.;
by usubjid;
run;

data test;
length EPOCH $30;
merge &dsn.(in=a) ec(in=b);
by usubjid;
if a;
%if &dsn.dtc ne '' %then %do; 
_date=substr(&dsn.DTC,1,10);
%end;
%else %if &dsn.stdtc ne '' %then %do;
_date=substr(&dsn.stdtc,1,10);
%end;
_date2=input(_date,yymmdd10.);
format _date2 yymmdd10.;
_days=_ecstd-_date2 +1;
if -42 <= _days < -21 then EPOCH="SCREENING";
else if -21 <= _days <=0 then EPOCH="BASELINE";
else if 0 < _days <= 730 then EPOCH="SHORT TERM FOLLOW-UP";
else if 730 < _days <= 2190 then EPOCH="LONG TERM FOLLOW-UP";
*drop _days _ecstd _date _date2;
run;

%mend;
%util_generate_epoch(dsn=ae);

[ballardw]

Just what do these variables look like?

Any time I see "date" mentioned with string functions I cringe because if often means someone is doing something bordering on unnatural.

[Tom · Accepted solution]

Are you trying to create a new variable in the dataset? Or change the value of a variable that already exists? What if the original dataset has both of those variables? Which value do you want to use?

Your %IF statement conditions can never be true.

%if &dsn.dtc ne '' %then %do; 

No matter what value the macro variable DSN have you are comparing one string of letters that always include the letters dtc to the a pair of single quote characters.  Those can never be true.

If you just want to populate DATE with which ever or AEDTC or AESTDTC is populated just use the COALESCEC() function.

_DATE = coalescec(&dsn.DTC,&dsn.STDTC);

If you want to force both AEDTC and AESTDTC to exist without knowing whether or not either exists or not one trick might be to add a FORMAT statement after the MERGE statement.  This will not modify the variable if it already exists, but it will let SAS guess to make a new variable with the right type and length if one does not already exist.  Not an ideal solution but a lot easier than querying the SAS metadata to find out if the variable exists or not.