Create multiple IF statements for each argument in VaRList macro varaibble

[Ronein]

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;

[Amir]

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.

[Kurt_Bremser]

See my answer to your previous question.

Dataset. Data Step. Call Execute.

[ed_sas_member]

@Ronein 

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;

[Ronein]

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;

[Kurt_Bremser · Accepted solution]

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.

[Ronein]

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;

[Kurt_Bremser]

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;