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;
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;
First thing I see if that you execute the macro directly after the definition unnecessarily.
Please include a log of the error.
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,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 2; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
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<"20AUG2019"d then Y else 0 end ) as Accum_Y_Til"20AUG2019"d,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 3; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
2 The SAS System 09:04 Wednesday, January 1, 2020
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<"25AUG2019"d then Y else 0 end ) as Accum_Y_Til"25AUG2019"d,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 4; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
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<"02SEP2019"d then Y else 0 end ) as Accum_Y_Til"02SEP2019"d,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 5; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
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<"05SEP2019"d then Y else 0 end ) as Accum_Y_Til"05SEP2019"d,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 6; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
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<"10SEP2019"d then Y else 0 end ) as Accum_Y_Til"10SEP2019"d,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 7; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
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<"15SEP2019"d then Y else 0 end ) as Accum_Y_Til"15SEP2019"d,;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MLOGIC(MMACRO): %DO loop index variable J is now 8; loop will not iterate again.
MLOGIC(MMACRO): Ending execution.
35
36
37 PROC SQL;
38 create table Wanted as
39 select ID,%mmacro
MLOGIC(MMACRO): Beginning execution.
MLOGIC(MMACRO): %DO loop beginning; index variable J; start value is 1; stop value is 7; by value is 1.
MLOGIC(MMACRO): %LET (variable name is DDATE)
3 The SAS System 09:04 Wednesday, January 1, 2020
MPRINT(MMACRO): sum(case when DateAction<"15AUG2019"d then Y else 0 end ) as Accum_Y_Til"15AUG2019"d,;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "DDATE".
39 Accum_Y_Til"15AUG2019"d
____________
22
76
MLOGIC(MMACRO): %DO loop index variable J is now 2; loop will iterate again.
MLOGIC(MMACRO): %LET (variable name is DDATE)
NOTE: Line generated by the invoked macro "MMACRO".
39 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;
___
180
MPRINT(MMACRO): sum(case when DateAction<"20AUG2019"d then Y else 0 end ) as Accum_Y_Til"20AUG2019"d,;
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 180-322: Statement is not valid or it is used out of proper order.
Remove the macro call that you make immediately after the definition. You also have a comma after ddate. (the second one).
see what happens after removing that
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;
perfect!
thank you all
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.