topic Re: Automat sum case statements with macro or with array in SAS Programming

topic Re: Automat sum case statements with macro or with array in SAS Programming https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614648#M179741 <P>I think there is no point to show log when the code is totally wrong.</P> <P>But as you asked i will how it</P> <P>NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"15AUG2019"d then Y else 0 end ) as Accum_Y_Til"15AUG2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 2; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"20AUG2019"d then Y else 0 end ) as Accum_Y_Til"20AUG2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 3; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />2 The SAS System 09:04 Wednesday, January 1, 2020</P> <P>NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"25AUG2019"d then Y else 0 end ) as Accum_Y_Til"25AUG2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 4; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"02SEP2019"d then Y else 0 end ) as Accum_Y_Til"02SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 5; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"05SEP2019"d then Y else 0 end ) as Accum_Y_Til"05SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 6; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"10SEP2019"d then Y else 0 end ) as Accum_Y_Til"10SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 7; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"15SEP2019"d then Y else 0 end ) as Accum_Y_Til"15SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 8; loop will not iterate again.<BR />MLOGIC(MMACRO): Ending execution.<BR />35 <BR />36 <BR />37 PROC SQL;<BR />38 create table Wanted as<BR />39 select ID,%mmacro<BR />MLOGIC(MMACRO): Beginning execution.<BR />MLOGIC(MMACRO): %DO loop beginning; index variable J; start value is 1; stop value is 7; by value is 1. <BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />3 The SAS System 09:04 Wednesday, January 1, 2020</P> <P>MPRINT(MMACRO): sum(case when DateAction<"15AUG2019"d then Y else 0 end ) as Accum_Y_Til"15AUG2019"d,;<BR />NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.<BR />NOTE: Line generated by the macro variable "DDATE".<BR />39 Accum_Y_Til"15AUG2019"d<BR />____________<BR />22<BR />76<BR />MLOGIC(MMACRO): %DO loop index variable J is now 2; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />39 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"20AUG2019"d then Y else 0 end ) as Accum_Y_Til"20AUG2019"d,;<BR />ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, <BR />LENGTH, TRANSCODE.</P> <P>ERROR 76-322: Syntax error, statement will be ignored.</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> Wed, 01 Jan 2020 08:30:31 GMT Ronein 2020-01-01T08:30:31Z Automat sum case statements with macro or with array https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614643#M179738 <P>Hello</P> <P>I am trying to create automat code but there is an error.</P> <P>What is the correct way to do it please?</P> <P>Should I  use macro or array?</P> <P>What is the correct code to do it?</P> <P> </P> <P> </P> <PRE><CODE class=" language-sas">%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;</CODE></PRE> Wed, 01 Jan 2020 07:45:32 GMT https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614643#M179738 Ronein 2020-01-01T07:45:32Z Re: Automat sum case statements with macro or with array https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614647#M179740 <P>First thing I see if that you execute the macro directly after the definition unnecessarily. </P> <P>Please include a log of the error.</P> Wed, 01 Jan 2020 08:08:21 GMT https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614647#M179740 unison 2020-01-01T08:08:21Z Re: Automat sum case statements with macro or with array https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614648#M179741 <P>I think there is no point to show log when the code is totally wrong.</P> <P>But as you asked i will how it</P> <P>NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"15AUG2019"d then Y else 0 end ) as Accum_Y_Til"15AUG2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 2; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"20AUG2019"d then Y else 0 end ) as Accum_Y_Til"20AUG2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 3; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />2 The SAS System 09:04 Wednesday, January 1, 2020</P> <P>NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"25AUG2019"d then Y else 0 end ) as Accum_Y_Til"25AUG2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 4; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"02SEP2019"d then Y else 0 end ) as Accum_Y_Til"02SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 5; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"05SEP2019"d then Y else 0 end ) as Accum_Y_Til"05SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 6; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"10SEP2019"d then Y else 0 end ) as Accum_Y_Til"10SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 7; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />34 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"15SEP2019"d then Y else 0 end ) as Accum_Y_Til"15SEP2019"d,;</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> <P>MLOGIC(MMACRO): %DO loop index variable J is now 8; loop will not iterate again.<BR />MLOGIC(MMACRO): Ending execution.<BR />35 <BR />36 <BR />37 PROC SQL;<BR />38 create table Wanted as<BR />39 select ID,%mmacro<BR />MLOGIC(MMACRO): Beginning execution.<BR />MLOGIC(MMACRO): %DO loop beginning; index variable J; start value is 1; stop value is 7; by value is 1. <BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />3 The SAS System 09:04 Wednesday, January 1, 2020</P> <P>MPRINT(MMACRO): sum(case when DateAction<"15AUG2019"d then Y else 0 end ) as Accum_Y_Til"15AUG2019"d,;<BR />NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.<BR />NOTE: Line generated by the macro variable "DDATE".<BR />39 Accum_Y_Til"15AUG2019"d<BR />____________<BR />22<BR />76<BR />MLOGIC(MMACRO): %DO loop index variable J is now 2; loop will iterate again.<BR />MLOGIC(MMACRO): %LET (variable name is DDATE)<BR />NOTE: Line generated by the invoked macro "MMACRO".<BR />39 sum(case when DateAction<&ddate. then Y else 0 end ) as Accum_Y_Til&ddate.,;<BR />___<BR />180<BR />MPRINT(MMACRO): sum(case when DateAction<"20AUG2019"d then Y else 0 end ) as Accum_Y_Til"20AUG2019"d,;<BR />ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, <BR />LENGTH, TRANSCODE.</P> <P>ERROR 76-322: Syntax error, statement will be ignored.</P> <P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P> Wed, 01 Jan 2020 08:30:31 GMT https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614648#M179741 Ronein 2020-01-01T08:30:31Z Re: Automat sum case statements with macro or with array https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614649#M179742 <P>Remove the macro call that you make immediately after the definition. You also have a comma after ddate. (the second one).</P> <P> </P> <P>see what happens after removing that</P> Wed, 01 Jan 2020 09:00:43 GMT https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614649#M179742 unison 2020-01-01T09:00:43Z Re: Automat sum case statements with macro or with array https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614654#M179746 <P>Hi <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/159549">@Ronein</a> </P> <P> </P> <P>Happy new year!</P> <P> </P> <P>Here is an attempt to automatize the calculation:</P> <PRE><CODE class=" language-sas">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;</CODE></PRE> Wed, 01 Jan 2020 10:38:00 GMT https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614654#M179746 ed_sas_member 2020-01-01T10:38:00Z Re: Automat sum case statements with macro or with array https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614659#M179751 <PRE><CODE class=" language-sas"></CODE></PRE> <P>perfect!</P> <P>thank you all</P> Wed, 01 Jan 2020 12:41:32 GMT https://communities.sas.com/t5/SAS-Programming/Automat-sum-case-statements-with-macro-or-with-array/m-p/614659#M179751 Ronein 2020-01-01T12:41:32Z