Following only this part, PROC SQL; CREATE TABLE HIST_&SUFF_3Y. AS SELECT PARTYID, SUM(DERIVED_GI_ANNUAL_GWP) AS HISTSUM FROM EVERMOT_PREMPF_NODUP WHERE YEAR IN &RANGE_3Y. GROUP BY PARTYID ; RUN; QUIT; What would be place in &SUFF_3Y. ? A figure from the macro or a column name? I tried running the macro in SAS Studio but it generates no result. Thank you.  ... View more

Hi, %macro HISTPREMPF(EFFYEAR); %LET YEAREFF=&EFFYEAR.; %LET SUFF_3Y=%EVAL(&YEAREFF.)_3Y; %PUT &SUFF_3Y.; %LET RANGE_3Y=(%EVAL(%EVAL(&YEAREFF.)-2),%EVAL(%EVAL(&YEAREFF.)-1)); %PUT &RANGE_3Y.; %LET SUFF_5Y=&YEAREFF._5Y; %LET RANGE_5Y=(%EVAL(%EVAL(&YEAREFF.)-4),%EVAL(%EVAL(&YEAREFF.)-3),%EVAL(%EVAL(&YEAREFF.)-2),%EVAL(%EVAL(&YEAREFF.)-1)); %PUT &RANGE_5Y.; %LET SUFF_10Y=&YEAREFF._10Y; %LET RANGE_10Y=(%EVAL(%EVAL(&YEAREFF.)-9),%EVAL(%EVAL(&YEAREFF.)-8),%EVAL(%EVAL(&YEAREFF.)-7),%EVAL(%EVAL(&YEAREFF.)-6),%EVAL(%EVAL(&YEAREFF.)-5),%EVAL(%EVAL(&YEAREFF.)-4),%EVAL(%EVAL(&YEAREFF.)-3),%EVAL(%EVAL(&YEAREFF.)-2),%EVAL(%EVAL(&YEAREFF.)-1)); %PUT &RANGE_5Y.; PROC SQL; CREATE TABLE HIST_&SUFF_3Y. AS SELECT PARTYID, SUM(DERIVED_GI_ANNUAL_GWP) AS HISTSUM FROM EVERMOT_PREMPF_NODUP WHERE YEAR IN &RANGE_3Y. GROUP BY PARTYID ; RUN; QUIT; PROC SQL; CREATE TABLE HIST_&SUFF_5Y. AS SELECT PARTYID, SUM(DERIVED_GI_ANNUAL_GWP) AS HISTSUM FROM EVERMOT_PREMPF_NODUP WHERE YEAR IN &RANGE_5Y. GROUP BY PARTYID ; RUN; QUIT; PROC SQL; CREATE TABLE HIST_&SUFF_10Y. AS SELECT PARTYID, SUM(DERIVED_GI_ANNUAL_GWP) AS HISTSUM FROM EVERMOT_PREMPF_NODUP WHERE YEAR IN &RANGE_10Y. GROUP BY PARTYID ; RUN; QUIT; PROC SQL; CREATE TABLE HIST_&YEAREFF. AS SELECT PARTYID, SUM(DERIVED_GI_ANNUAL_GWP) AS HISTSUM FROM EVERMOT_PREMPF_NODUP WHERE YEAR <&YEAREFF. GROUP BY PARTYID ; RUN; QUIT; %LET TAB_BASE=BASE; %LET TAB_TARGET=BASE_1; PROC SQL; CREATE TABLE &TAB_TARGET. AS SELECT &TAB_BASE..*, HISTSUM AS HISTGWP_&SUFF_3Y. FROM &TAB_BASE. LEFT JOIN HIST_&SUFF_3Y. ON &TAB_BASE..PARTYID=HIST_&SUFF_3Y..PARTYID AND YEAR(&TAB_BASE..EFFECTIVEDATE)=&YEAREFF. ; RUN;QUIT; %LET TAB_BASE=BASE_1; %LET TAB_TARGET=BASE_2; PROC SQL; CREATE TABLE &TAB_TARGET. AS SELECT &TAB_BASE..*, HISTSUM AS HISTGWP_&SUFF_5Y. FROM &TAB_BASE. LEFT JOIN HIST_&SUFF_5Y. ON &TAB_BASE..PARTYID=HIST_&SUFF_5Y..PARTYID AND YEAR(&TAB_BASE..EFFECTIVEDATE)=&YEAREFF. ; RUN;QUIT; %LET TAB_BASE=BASE_2; %LET TAB_TARGET=BASE_3; PROC SQL; CREATE TABLE &TAB_TARGET. AS SELECT &TAB_BASE..*, HISTSUM AS HISTGWP_&SUFF_10Y. FROM &TAB_BASE. LEFT JOIN HIST_&SUFF_10Y. ON &TAB_BASE..PARTYID=HIST_&SUFF_10Y..PARTYID AND YEAR(&TAB_BASE..EFFECTIVEDATE)=&YEAREFF. ; RUN;QUIT; %LET TAB_BASE=BASE_3; %LET TAB_TARGET=BASE_4; PROC SQL; CREATE TABLE &TAB_TARGET. AS SELECT &TAB_BASE..*, HISTSUM AS HISTGWP_&YEAREFF._ALL FROM &TAB_BASE. LEFT JOIN HIST_&YEAREFF. ON &TAB_BASE..PARTYID=HIST_&YEAREFF..PARTYID AND YEAR(&TAB_BASE..EFFECTIVEDATE)=&YEAREFF. ; RUN;QUIT; DATA BASE; SET BASE_4; RUN; PROC DELETE DATA=BASE_1 BASE_2 BASE_3 BASE_4 HIST_&SUFF_3Y. HIST_&SUFF_5Y. HIST_&SUFF_10Y. HIST_&YEAREFF.; %mend; %HISTPREMPF(&HIST_ASOF_YEAR.); RUN; QUIT; This is the complete code. I did love to try to run the code..but there are some issue with my SAS studio, the output result are not display after execution. Thank you. ... View more

Thanks a lot for the clarification! ... View more

How do I change the MISSOVER? ... View more

In the original source csv, there are only 10411 rows. ... View more