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;
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
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 ?
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
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;
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 !
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.