Hello
User define vector of dates.
For each of the argument I need to produce IF statement.
%let vector1='20NOV2019'd+'25NOV2019'd+'02DEC2019'd+'05DEC2019'd+'10DEC2019'd+'15DEC2019'd;
%let vector2=20NOV2019+25NOV2019+02DEC2019+05DEC2019+10DEC2019+15DEC2019;
%let n=6;
%put &vector1;
%put &vector2;
%put &n;
/*I need to produce following statements */
/*Then I will use it */
/*IF OFFER_DATE='20NOV2019'd then Accum_Loan=AccumTill_20NOV2019;*/
/*IF OFFER_DATE='25NOV2019'd then Accum_Loan=AccumTill_25NOV2019;*/
/*IF OFFER_DATE='02DEC2019'd then Accum_Loan=AccumTill_02DEC2019;*/
/*IF OFFER_DATE='05DEC2019'd then Accum_Loan=AccumTill_05DEC2019;*/
/*IF OFFER_DATE='10DEC2019'd then Accum_Loan=AccumTill_10DEC2019;*/
/*IF OFFER_DATE='15DEC2019'd then Accum_Loan=AccumTill_15DEC2019;*/
%macro mmacro;
%do j=1 %to &n.;
%let date=%scan(&vector1.,&j.,+);
%let dateName=%scan(&vector2.,&j.,+);
IF OFFER_DATE=&date. then Accum_Loan=AccumTill_&date.;
%end;
%mend;
%put %mmacro;
/***I don't get the list of the statements as I want*/
Data b;
set a;
%mmacro;
Run;
The ERRORs are caused by this:
%put %mmacro;
as you call the macro outside of a data step, and it creates data step code. Remove this unnecessary statement.
To get a macro to work, ALWAYS START WITH VALID, WORKING, NON-MACRO SAS CODE!!!
Create a data step that works for a few of your dates, THEN make it dynamic.
if offer_date = 21873 then accum_loan = accum_til_20NOV2019;
will only work if accum_til_20NOV2019 is already present in the dataset.
Thanks for supplying code and what you expected.
It looks like you should have used dateName at the end of:
IF OFFER_DATE=&date. then Accum_Loan=AccumTill_&date.;
So that it becomes:
IF OFFER_DATE=&date. then Accum_Loan=AccumTill_&dateName.;
If this is not your issue then please show your log of the code being executed including any error messages.
Regards,
Amir.
See my answer to your previous question.
Dataset. Data Step. Call Execute.
Maybe you can try this, to avoid putting formatted dates into macro variables (this could play trick on you)
/* UNTESTED CODE */
data dates;
input date:date9.;
datalines;
20NOV2019
25NOV2019
02DEC2019
05DEC2019
10DEC2019
15DEC2019
;
run;
data _null_;
set dates;
call symputx("date"||left(_n_),date);
call symputx("datef"||left(_n_),put(date,date9.));
call symputx("nobs",_n_);
run;
data b;
set a;
do i=1 to &nobs;
if OFFER_DATE=&&date&i then Accum_Loan=AccumTill_&&datef&i;
end;
run;
Thank you very much.
Unfortunately the last step is not working and I get an error.
Please find the code that I run.
data a;
informat OFFER_DATE date9.;
format OFFER_DATE date9.;
input ID OFFER_DATE offer_Amount
AccumTil_20NOV2019 AccumTil_25NOV2019 AccumTil_02DEC2019
AccumTil_05DEC2019 AccumTil_10DEC2019 AccumTil_15DEC2019;
cards;
1 '20NOV2019'd 10 0 0 0 0 5 7
2 '20NOV2019'd 20 2 3 4 5 6 7
3 '10DEC2019'd 30 0 0 0 3 3 3
4 '15DEC2019'd 40 0 0 0 0 0 0
5 '10DEC2019'd 50 0 0 0 0 0 0
6 '15DEC2019'd 60 0 0 0 0 0 0
7 '15DEC2019'd 70 0 0 0 0 0 0
8 '15DEC2019'd 80 0 0 0 0 0 0
9 '10DEC2019'd 90 0 7 8 9 9 9
10 '10DEC2019'd 100 0 0 0 0 0 0
;
run;
data dates;
input date:date9.;
datalines;
20NOV2019
25NOV2019
02DEC2019
05DEC2019
10DEC2019
15DEC2019
;
run;
data _null_;
set dates;
call symputx("date"||left(_n_),date);
call symputx("datef"||left(_n_),put(date,date9.));
call symputx("nobs",_n_);
run;
%put &date1;
%put &date2;
%put &date3;
%put &date4;
%put &date5;
%put &date6;
%put &datef1;
%put &datef2;
%put &datef3;
%put &datef4;
%put &datef5;
%put &datef6;
%put &nobs;
/*IT is not working!! error*/
/*ERROR 180-322: Statement is not valid or it is used out of proper order.*/
%macro mmacro;
%do i=1 %to &nobs.;
if OFFER_DATE=&&date&i.. then Accum_Loan=AccumTil_&&datef&i..;
%end;
%mend;
%put %mmacro;
data b;
set a;
%mmacro;
run;
/*It is also not working*/
data b;
set a;
do i=1 to &nobs;
if OFFER_DATE=&&date&i then Accum_Loan=AccumTill_&&datef&i;
end;
Run;
/*By writing the IF statements manually it is working well*/
data b;
set a;
IF OFFER_DATE='20NOV2019'd then Accum_SUM_HALV=AccumTil_20NOV2019;
IF OFFER_DATE='25NOV2019'd then Accum_SUM_HALV=AccumTil_25NOV2019;
IF OFFER_DATE='02DEC2019'd then Accum_SUM_HALV=AccumTil_02DEC2019;
IF OFFER_DATE='05DEC2019'd then Accum_SUM_HALV=AccumTil_05DEC2019;
IF OFFER_DATE='10DEC2019'd then Accum_SUM_HALV=AccumTil_10DEC2019;
IF OFFER_DATE='15DEC2019'd then Accum_SUM_HALV=AccumTil_15DEC2019;
Run;
The ERRORs are caused by this:
%put %mmacro;
as you call the macro outside of a data step, and it creates data step code. Remove this unnecessary statement.
To get a macro to work, ALWAYS START WITH VALID, WORKING, NON-MACRO SAS CODE!!!
Create a data step that works for a few of your dates, THEN make it dynamic.
if offer_date = 21873 then accum_loan = accum_til_20NOV2019;
will only work if accum_til_20NOV2019 is already present in the dataset.
Hello
Thank you so much.
Is there a way to fix the code that way3 and way4 will work also?
data a;
informat OFFER_DATE date9.;
format OFFER_DATE date9.;
input ID OFFER_DATE offer_Amount
AccumTill_20NOV2019 AccumTill_25NOV2019 AccumTill_02DEC2019
AccumTill_05DEC2019 AccumTill_10DEC2019 AccumTill_15DEC2019;
cards;
1 '20NOV2019'd 10 0 0 0 0 5 7
2 '20NOV2019'd 20 2 3 4 5 6 7
3 '10DEC2019'd 30 0 0 0 3 3 3
4 '15DEC2019'd 40 0 0 0 0 0 0
5 '10DEC2019'd 50 0 0 0 0 0 0
6 '15DEC2019'd 60 0 0 0 0 0 0
7 '15DEC2019'd 70 0 0 0 0 0 0
8 '15DEC2019'd 80 0 0 0 0 0 0
9 '10DEC2019'd 90 0 7 8 9 9 9
10 '10DEC2019'd 100 0 0 0 0 0 0
;
run;
data dates;
input date:date9.;
datalines;
20NOV2019
25NOV2019
02DEC2019
05DEC2019
10DEC2019
15DEC2019
;
run;
data _null_;
set dates;
call symputx("date"||left(_n_),date);
call symputx("datef"||left(_n_),put(date,date9.));
call symputx("nobs",_n_);
run;
%put &date1;
%put &date2;
%put &date3;
%put &date4;
%put &date5;
%put &date6;
%put &datef1;
%put &datef2;
%put &datef3;
%put &datef4;
%put &datef5;
%put &datef6;
%put &nobs;
/*Way1-Maunal writing IF statements*/
/*By writing the IF statements manually it is working well*/
data b;
set a;
IF OFFER_DATE='20NOV2019'd then Accum_SUM_HALV=AccumTill_20NOV2019;
IF OFFER_DATE='25NOV2019'd then Accum_SUM_HALV=AccumTill_25NOV2019;
IF OFFER_DATE='02DEC2019'd then Accum_SUM_HALV=AccumTill_02DEC2019;
IF OFFER_DATE='05DEC2019'd then Accum_SUM_HALV=AccumTill_05DEC2019;
IF OFFER_DATE='10DEC2019'd then Accum_SUM_HALV=AccumTill_10DEC2019;
IF OFFER_DATE='15DEC2019'd then Accum_SUM_HALV=AccumTill_15DEC2019;
Run;
/*Way2*/
/*It is working well*/
%macro want();
%do i=1 %to &nobs;
Data wanted_&i(Keep=ID AccumTill_&&datef&i);
set a;
if OFFER_DATE=&&date&i then Accum_Loan=AccumTill_&&datef&i;
Run;
%end;
%mend;
%want
data b;
merge a wanted_: ;
by ID;
Run;
/*Way3*/
/*IT is not working!! error*/
/*ERROR 180-322: Statement is not valid or it is used out of proper order.*/
%macro mmacro;
%do i=1 %to &nobs.;
if OFFER_DATE=&&date&i.. then Accum_Loan=AccumTill_&&datef&i..;
%end;
%mend;
%put %mmacro;
data b;
set a;
%mmacro;
run;
/*Way4*/
/*It is also not working*/
data b;
set a;
do i=1 to &nobs;
if OFFER_DATE=&&date&i then Accum_Loan=AccumTill_&&datef&i;
end;
Run;
You still have that wrong %put in there, causing the ERRORs in way 3.
Way 4 can't work, you can't mix data step and macro code like this; we've covered that a gazillion times by now.
THIS WORKS:
data a;
informat OFFER_DATE date9.;
format OFFER_DATE date9.;
input ID OFFER_DATE offer_Amount
AccumTill_20NOV2019 AccumTill_25NOV2019 AccumTill_02DEC2019
AccumTill_05DEC2019 AccumTill_10DEC2019 AccumTill_15DEC2019;
cards;
1 '20NOV2019'd 10 0 0 0 0 5 7
2 '20NOV2019'd 20 2 3 4 5 6 7
3 '10DEC2019'd 30 0 0 0 3 3 3
4 '15DEC2019'd 40 0 0 0 0 0 0
5 '10DEC2019'd 50 0 0 0 0 0 0
6 '15DEC2019'd 60 0 0 0 0 0 0
7 '15DEC2019'd 70 0 0 0 0 0 0
8 '15DEC2019'd 80 0 0 0 0 0 0
9 '10DEC2019'd 90 0 7 8 9 9 9
10 '10DEC2019'd 100 0 0 0 0 0 0
;
data dates;
input date:date9.;
datalines;
20NOV2019
25NOV2019
02DEC2019
05DEC2019
10DEC2019
15DEC2019
;
data _null_;
set dates end=done;
if _n_ = 1 then call execute('data b; set a;');
call execute(
'if offer_date = ' !! put(date,5.) !! ' then accum_loan = accumtill_' !! put(date,date9.) !! ';'
);
if done then call execute('run;');
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.