VarList macro variable

[Ronein]

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;
 

[PaigeMiller]

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.

[Ronein]

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;

[Tom]

Use the data to generate the code to a file. Then %INCLUDE the file.

Here is an example.  A few pointers:

• SAS will evaluate boolean expressions to 1 for TRUE and 0 for FALSE.  So to count the number of TRUE results just sum the boolean expression. No need to complicate things by trying to get the CASE syntax right (your example code is referencing a variable named Y that I doubt you have.)
• Placing commas at the start of the line instead of the end makes this code much easier to generate, no need to treat the code generated for the last date in the list any differently from the code generated for the other dates.
• If you use date strings in Y-M-D order when generating text (for example as part of a variable name) it will insure that they appear in chronological order when ordered alphabetically.

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. 
  ;
...

[Tom]

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;

[ErikLund_Jensen]

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