Hello
User define FROM and Until macro variables.
Then all days of 2/5/10/15/20/25 between FROM and Until are calculated .
Then We put the values in VarList macro varaible (with + between values)
Now I will ask the question.
I need to create SUM statements (with case when ).
Number of statements must be equal to number of arguments in VarList macro variable.
What is the way to create a code that run it automatically ?
%let From='24OCT2019'd;
%let Until='17NOV2019'd;
data list ;
do date=&from to &until ;
if day(date) in (2 5 10 15 20 25) then output;
end;
run;
proc sql noprint;
select cats(quote(put(date,date9.)),'d')
, put(date,yymmdd6.)
into :varlist1 separated by '+'
, :varlist2 separated by '+'
from list
;
quit;
%put &varlist1;
/*"25OCT2019"d+"02NOV2019"d+"05NOV2019"d+"10NOV2019"d+"15NOV2019"d*/
PROC SQL;
create table Wanted as
select ID,
sum(case when Date<'25OCT2019'd then Y else 0 end ) as AccumY_Til_25OCT2019,
sum(case when Date<'02NOV2019'd then Y else 0 end ) as AccumY_Til_02NOV2019,
sum(case when Date<'05NOV2019'd then Y else 0 end ) as AccumY_Til_05NOV2019,
sum(case when Date<'10NOV2019'd then Y else 0 end ) as AccumY_Til_10NOV2019,
sum(case when Date<'15NOV2019'd then Y else 0 end ) as AccumY_Til_15NOV2019
from tbl
group by ID
;
QUIT;
So this is why you wanted that macro variable to have a string of dates with + signs, so you could pull the string apart later and perform this summing? You could have just created individual macro variables and then you wouldn't have to append them into a long string and then pull the string apart later.
But ...
You could identify the dates of interest with a data step (seems like you have done that), then storing those desired dates in the SAS data set created, instead of storing them in a large unwieldy macro variable. Then you could use CALL EXECUTE and PROC SUMMARY to compute the sums.
No macros needed.
Thank you.
The macro is needed in other steps and my question is how to do it from the ListValues macro variable or alternatively from a data set that contain list of the values (dates)
So let's say that I have a data set with values of dates.
How can I use this table to create the SUM case when statements?
Data Helptbl;
informat ddate date9.;
format ddate date9.;
input ddate ddate_char $;
CARDS;
'25OCT2019'd 25OCT2019
'02NOV2019'd 02NOV2019
'05NOV2019'd 05NOV2019
'10NOV2019'd 10NOV2019
'15NOV2019'd 15NOV2019
;
Run;
PROC SQL;
create table Wanted as
select ID,
sum(case when Date<'25OCT2019'd then Y else 0 end ) as AccumY_Til_25OCT2019,
sum(case when Date<'02NOV2019'd then Y else 0 end ) as AccumY_Til_02NOV2019,
sum(case when Date<'05NOV2019'd then Y else 0 end ) as AccumY_Til_05NOV2019,
sum(case when Date<'10NOV2019'd then Y else 0 end ) as AccumY_Til_10NOV2019,
sum(case when Date<'15NOV2019'd then Y else 0 end ) as AccumY_Til_15NOV2019
from RawTbl
group by ID
;
QUIT;
Use the data to generate the code to a file. Then %INCLUDE the file.
Here is an example. A few pointers:
data helptbl;
input ddate :date. ;
format ddate date9.;
cards;
25OCT2019
02NOV2019
05NOV2019
10NOV2019
15NOV2019
;
filename code temp;
data _null_;
file code;
set helptbl end=eof;
if _n_=1 then put 'create table Wanted as select ID' ;
put ",sum(Date<'" ddate date9. "'d) as AccumY_Til_" ddate yymmddn8. ;
if eof then put ' from RawTbl group by ID;' ;
run;
How to use the generated CREATE statement.
proc sql ;
%include code / source2;
quit;
Log:
522 proc sql ; 523 %include code / source2; NOTE: %INCLUDE (level 1) file CODE is file C:\...\#LN00059. 524 +create table Wanted as select ID 525 +,sum(Date<'25OCT2019'd) as AccumY_Til_20191025 526 +,sum(Date<'02NOV2019'd) as AccumY_Til_20191102 527 +,sum(Date<'05NOV2019'd) as AccumY_Til_20191105 528 +,sum(Date<'10NOV2019'd) as AccumY_Til_20191110 529 +,sum(Date<'15NOV2019'd) as AccumY_Til_20191115 530 + from RawTbl group by ID; ERROR: File WORK.RAWTBL.DATA does not exist. NOTE: %INCLUDE (level 1) ending. 531 quit; NOTE: The SAS System stopped processing this step because of errors.
If you really do have a variable named Y and want to make the generated name and the variable reference flexible and coordinated you might want to use a macro variable to store that name.
%let accumvar=Y;
....
put ",sum(case when (Date<'" ddate date9.
"'d) then &accumvar. else 0 end) as Accum&accumvar._Til_" ddate yymmddn8.
;
...
Note that someone should be able to show you how to join the two tables and generate a separate observation for each DATE in the metadata table, instead of a separate variable. You could then produce your wide format as either a report or by using PROC TRANSPOSE. That solution would not need any code generation.
Probably something like this:
proc sql ;
create table tall as
select a.id,b.ddate,sum(coalesce(a.y,0)) as accumY
from RawTbl a full join helptbl b
on a.date < b.ddate
group by a.id,b.ddate
order by a.id,b.ddate
;
quit;
proc transpose data=tall out=want prefix=AccumY_Til_;
by id;
id ddate ;
var accumY;
format ddate yymmddn8. ;
run;
Hi @Ronein
Because it's new near will try to give a solution following your lines of thought instead af suggesting alternative approaches. I have added code to generate a test input data set to prove it works.
The first part is to translate the user input dates to corresponding SUM statements. I use one step, but if you for some reason would prefer a list of dates in a table, just remove the do loop in the code and add an input data set with date values. Note that I am not using date literals in the SQL statements, but instead use date formats to generate the new variablre names.:
The output macro variable CASELIST is built as a string of SUM statement separated by commas (but not terminated with comma, because that would generate a syntax error when then the macro variable is used as the last variable specification in SQL select).
This way, the macro variable can by used "as is" in a succeding SQL select, where it will resolve to the resuired sum statements:
sum(case when Date< 21847 then y else 0 end ) as AccumY_Til_25OCT2019,
sum(case when Date< 21855 then y else 0 end ) as AccumY_Til_02NOV2019,
sum(case when Date< 21858 then y else 0 end ) as AccumY_Til_05NOV2019,
sum(case when Date< 21863 then y else 0 end ) as AccumY_Til_10NOV2019,
sum(case when Date< 21868 then y else 0 end ) as AccumY_Til_15NOV2019
* Make some test data to see it actually working... ;
data have;
do ID = 1 to 3;
do date = '01oct2019'd to '01dec2019'd;
y = (date-'01oct2019'd) * ID;
output;
end;
end;
run;
* User define FROM and Until macro variables;
%let From='24OCT2019'd;
%let Until='17NOV2019'd;
* Then all days of 2/5/10/15/20/25 between FROM and Until are calculated;
* - And SUM statements (with case when ) are created
* - And put together in a macro variable separated vith commas;
data _null_;
length str $32000;
retain str;
do date=&from to &until ;
if day(date) in (2 5 10 15 20 25) then str =
catx(',',str,'sum(case when Date<'||put(date,6.)||' then y else 0 end ) as AccumY_Til_' || put(date,date9.));
end;
call symput('caselist',trim(str));
run;
%put &=caselist;
* Now the generated macro variable can be used in a SQL select;
* Note that according to SQL syntax, there is no comma after the last Sum statement;
proc sql;
create table Wanted as
select ID,
&caselist
from have
group by ID;
quit;
Happy New Year
Erik
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.