Solved: Automat sum case statements with macro or with array

Ronein · Posted 01-01-2020 02:45 AM

Hello

I am trying to create automat code but there is an error.

What is the correct way to do it please?

Should I use macro or array?

What is the correct code to do it?

%let VarList=21776+21781+21786+21794+21797+21802+21807;
%let VarList2=190815+190820+190825+190902+190905+190910+190915;
%let count = %sysfunc(countw(&VarList));


Data RawTbl;
informat  DateAction date9.; 
format DateAction date9.; 
Input ID DateAction Y;
Cards;
1 '12AUG2019'd 10
1 '13AUG2019'd 20
1 '14AUG2019'd 30
1 '18AUG2019'd 40
1 '21AUG2019'd 50
1 '14SEP2019'd 60
2 '13AUG2019'd 70
2 '13AUG2019'd 80
2 '14AUG2019'd 90
2 '14AUG2019'd 100
2 '17AUG2019'd 110
2 '14SEP2019'd 120
;
Run;

/*Way to perform maunally*/
PROC SQL;
	create table Wanted  as
	select ID,
       sum(case when DateAction<'15AUG2019'd then Y else 0 end ) as AccumTil_l5AUG2019,
	   sum(case when DateAction<'20AUG2019'd then Y else 0 end ) as AccumTil_20AUG2019,
	   sum(case when DateAction<'25AUG2019'd then Y else 0 end ) as AccumTil_25AUG2019,
	   sum(case when DateAction<'02SEP2019'd then Y else 0 end ) as AccumTil_02SEP2019,
	   sum(case when DateAction<'05SEP2019'd then Y else 0 end ) as AccumTil_05SEP2019,
	   sum(case when DateAction<'10SEP2019'd then Y else 0 end ) as AccumTil_10SEP2019,
	   sum(case when DateAction<'15SEP2019'd then Y else 0 end ) as AccumTil_15SEP2019
	from  RawTbl
	group by ID
;
QUIT;

/*Task-Automize*/
%macro mmacro; 
%do j=1 %to &count.;
%let ddate=%scan(&VarList.,&j.,+);
sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;
%end;
%mend mmacro;
%mmacro; 


PROC SQL;
	create table Wanted  as
	select ID,%mmacro
	from  RawTbl
	group by ID
;
QUIT;

ed_sas_member · Posted 01-01-2020 05:37 AM

Hi @Ronein

Happy new year!

Here is an attempt to automatize the calculation:

data date_ref_table;
	input date_ref:date9.;
	name_var = cats("AccumTil_", put(date_ref,date9.));
	datalines;
'15AUG2019'd
'20AUG2019'd
'25AUG2019'd
'02SEP2019'd
'05SEP2019'd
'10SEP2019'd
'15SEP2019'd
;
run;

proc sql noprint;
	select date_ref into: date_ref separated by " " from date_ref_table;
	select name_var into: list_var separated by " " from date_ref_table;
	select count(name_var) into: count from date_ref_table;
quit;


data RawTbl2;
	set RawTbl;
	array date_ref (&count) _temporary_ (&date_ref);
	array AccumTil_(*) &list_var;
	format date_ref: date9.;

	do i=1 to 7;

		if DateAction < date_ref(i) then AccumTil_(i)=Y;
		else AccumTil_(i)=0;
	end;
	
	drop i Y DateAction;
run;

proc means data=RawTbl2 sum noprint;
	var AccumTil_:;
	class id;
	ways 1;
	output out=want (drop=_type_ _freq_) sum=;
run;

View solution in original post

unison · Posted 01-01-2020 03:08 AM

First thing I see if that you execute the macro directly after the definition unnecessarily.

Please include a log of the error.

-unison

Ronein · Posted 01-01-2020 03:30 AM

I think there is no point to show log when the code is totally wrong.

But as you asked i will how it

NOTE: Line generated by the invoked macro "MMACRO".
34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;
___
180
MPRINT(MMACRO): sum(case when DateAction<"15AUG2019"d then Y else 0 end ) as Accum_Y_Til"15AUG2019"d,;