Trying to automate macro code

umashankersaini — Tue, 25 Jun 2019 18:28:17 GMT

Hi Team,

Kindly help me to apply %if,%then and %else condition on following case.

Step 1 : Creating macro variables for dates

data _null_;
END_DT = intnx('month',today(),0,'e');
END_DT_2LST = intnx('month',today(),-1,'e');
END_DT_CURR = intnx('month',today(),1,'e');
call symput('END_DT', "'"||put(END_DT,date9.)||"'d");
call symput('END_DT_2LST', "'"||put(END_DT_2LST,date9.)||"'d");
call symput('END_DT_CURR', "'"||put(END_DT_CURR,date9.)||"'d");
run;

data test;
CURR_ED = intnx('month',&END_DT.,-1,'e');
CURR_SD1 = intnx('month',&END_DT.,-1,'b');
LST_ED = intnx('month',&END_DT.,-2,'e');
LST_SD24 = intnx('month',&END_DT.,-25,'b');
call symput('CURR_ED', "'"||put(CURR_ED,date9.)||"'d");
call symput('CURR_SD1', "'"||put(CURR_SD1,date9.)||"'d");
call symput('LST_ED', "'"||put(LST_ED,date9.)||"'d");
call symput('LST_SD24', "'"||put(LST_SD24,date9.)||"'d");
run;

data _null_;
call symput ("SCR_MNTH", compress(put((intnx('MONTH',&CURR_ED.,1,'e')),monyy5.)));
call symput ("LST_MNTH",compress(put((intnx('MONTH',&CURR_ED.,0,'e')),monyy5.)));
call symput ("LST2_MNTH",compress(put((intnx('MONTH',&CURR_ED.,-1,'e')),monyy5.)));
run;

%macro data_prep(END_DT=);

Proc sql;

create table Tran_&LST_MNTH._DV as

Select cust_id, flg_del from source

where DATE between &LST_SD24. and &LST_ED.; quit;

%if "&END_DT."="&END_DT." %then %do;

Data Tran_&LST_MNTH._DV;

set Tran_&LST_MNTH._DV;

run;

%end;

%if "&END_DT."="&END_DT_2LST." %then %do;

Data Tran_&LST2_MNTH._DV;

set Tran_&LST_MNTH._DV;

where flg_del=1;

run;

%end;

%if "&END_DT."="&END_DT_CURR." %then %do;

Data Tran_&SCR_MNTH._DV;

set Tran_&LST_MNTH._DV;

where flg_del=0;

run;

%end;

%mend data_prep;

%data_prep(END_DT=&END_DT.);;

%data_prep(END_DT=&END_DT_2LST.);

%data_prep(END_DT=&END_DT_CURR.);

i have just given you the approach, i am trying with it.

It's a huge repetitive code, running for consecutive three months. Only one step is different. Due to only that, code has been written three times. Kindly help me so that i can reduce the length of code.

Re: Trying to automate macro code

ballardw — Wed, 26 Jun 2019 17:31:35 GMT

Please describe the general process of what you are actually attempting to do. Not just this bit of code but the overall process.

Often people will get tied up into attempting one solution that really isn't a good fit but comes close enough for one piece of a problem to keep pounding on it.

Actually including quotes into macro variables is often not a best practice and if you want to compare values with actual date variable then use the actual value is often much cleaner code.

Everything you do in those 3 data steps can be done in one with care.

Note that

%if "&END_DT."="&END_DT." %then %do;

is always true, you are comparing a variable to itself. Which is going to be true. So that particular statement is either incorrect or not needed at all.

Also using a macro parameter with the same name as another macro variable, such as your END_dt, can lead to unexpected results and is likely the cause of that problematic %if above.

It is also a poor idea to let external macro variables just "fall into" your code. It is better to explicitly pass them than the use you have here:

%macro data_prep(END_DT=);

Proc sql;
create table Tran_&LST_MNTH._DV as
Select cust_id, flg_del from source
where DATE between &LST_SD24. and &LST_ED.; quit;

where you use 3 macro variables that are not passed to the macro.

If you actually need quotes then the function quote is the way to go. Quote(value) instead of "'"||value||"'

You say

It's a huge repetitive code, running for consecutive three months. Only one step is different. Due to only that, code has been written three times. Kindly help me so that i can reduce the length of code.

I don't see any "huge repetive code". Are you leaving out something? Which step is different

topic Re: Trying to automate macro code in SAS Programming

Trying to automate macro code

Re: Trying to automate macro code