topic Re: %let ListDates and run multiple case when statements in SAS Programming

%let ListDates and run multiple case when statements

Ronein — Sun, 12 Jan 2020 10:32:32 GMT

Hello

There is a macro variable called ListDates.

%let ListDates=20NOV2019+25NOV2019+02DEC2019;

I need to create automatically following statements:

sum(case when date<'20NOV2019'd then SumLoan else 0 end ) as AccumTil_20NOV2019,
sum(case when date<'25NOV2019'd then SumLoan else 0 end ) as AccumTil_25NOV2019,
sum(case when date<'02DEC2019'd then SumLoan else 0 end ) as AccumTil_02DEC2019,

As you can see If in &ListDates there are more arguments then more sum case when statements will be created.

Then I will use these statements in proc sql.

PROC SQL;
create table wanted as
select ID,
sum(case when date<'20NOV2019'd then SumLoan else 0 end ) as AccumTil_20NOV2019,
sum(case when date<'25NOV2019'd then SumLoan else 0 end ) as AccumTil_25NOV2019,
sum(case when date<'02DEC2019'd then SumLoan else 0 end ) as AccumTil_02DEC2019
from RawTbl
group by ID
;
QUIT;

Re: %let ListDates and run multiple case when statements

ed_sas_member — Sun, 12 Jan 2020 11:14:20 GMT

Hi @Ronein

It is definitely a bad idea to store formatted dates in macrovariables.

Is it a requirement in your case?

Can't you use those dates in a SAS dataset ?

Re: %let ListDates and run multiple case when statements

ed_sas_member — Sun, 12 Jan 2020 11:31:23 GMT

Hi @Ronein

Here is a way to achieve this.

Hope this help.

Best,

data dates;
	input date:date9.;
	datalines;
20NOV2019
25NOV2019
02DEC2019
;
run;

data _null_;
	set dates;
	call symputx("date"||left(_n_),date);
	call symputx("datef"||left(_n_),put(date,date9.));
	call symputx("nobs",_n_);
run;

%macro want();

	%do i=1 %to &nobs;
		proc sql;
			create table wanted_&i as
			select ID,
				   sum(case when date<&&date&i then SumLoan else 0 end) as AccumTil_&&datef&i
			from RawTbl
			group by ID	;
		quit;
	%end;
	
	data wanted;
		merge wanted_:;
		by ID;
	run;
%mend;

%want

Re: %let ListDates and run multiple case when statements

ErikLund_Jensen — Sun, 12 Jan 2020 11:49:29 GMT

Hi @Ronein

I agree with @ed_sas_member , it is a bad idea to use formatted dates. You asked a similar question some time ago (https://communities.sas.com/t5/SAS-Programming/VarList-macro-variable/m-p/614679#M179756), and in the answer there you can see a similar solution with the date list read from a data set with real sas dates.

However, if you really want to use the formatted dates in a macro variable to directly generate the case statements, this is possible too. Here is a solution which works in only one pass over input, no macro loop:

* Date list;
%let ListDates=20NOV2019+25NOV2019+02DEC2019;

* Generate case statements;
data _null_;
	length date $9 str $32000;
	i = 0;
	do until (date='');
		i = i + 1;
		date = scan("&ListDates",i,'+');
		if date ne '' then str = catx(',',str,'sum(case when Date<"'||date||'"d then SumLoan else 0 end ) as Accum_Til_'||date);
	end;
	call symput('caselist',trim(str));
run;
%put &=caselist;

* Test the generated statements 1 - make test data;
data have;
	format date date9.;
	do ID = 1 to 3;
		do date = '10nov2019'd to '10dec2019'd;
			SumLoan = 12345 * ID;
			output;
		end;
	end;
run;

* Test the generated statements 2 - use in proc sql to see it actually working;
proc sql;
	create table want as
		select ID,
		&caselist
	from have
	group by ID;
quit;

Re: %let ListDates and run multiple case when statements

Kurt_Bremser — Sun, 12 Jan 2020 13:36:42 GMT

Drop your macro list obsession.

data dates;
	input date:date9.;
	datalines;
20NOV2019
25NOV2019
02DEC2019
;
run;

data _null_;
set dates end=done;
if _n_ = 1 then call execute('proc sql; create table wanted as select id');
call execute(',sum(case when date<'!!put(date,5.)!!' then sumloan else 0 end) as accumtil_'!!put(date,yymmddn8.));
if done then call execute(' from rawtbl group by id;quit;');
run;

See how simple the code becomes?

Ups, meant to be an answer to @Ronein !