topic Re: Question regarding Macro. in SAS Studio

Question regarding Macro.

Wken1122 — Thu, 25 Jan 2018 02:29:11 GMT

Hi,

Can someone please kindly explain what does this macro do?

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

There is a column, EFFECTIVEYEAR, is it equivalent to EFFYEAR?

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 placed into HIST_&SUFF_3Y. and &RANGE_3Y. ?

Thank you in advance.

Re: Question regarding Macro.

art297 — Thu, 25 Jan 2018 02:47:54 GMT

It would have the value used in the macro call:

%HISTPREMPF(xxx)

where you'd substitute xxx with some value or variable

Art, CEO, AnalystFinder.com

Re: Question regarding Macro.

Kurt_Bremser — Thu, 25 Jan 2018 08:41:46 GMT

Your macro code is not complete, at least the

%mend;

is missing.

Since SAS is an interpreting environment, there's one easy method: run the code and see what happens. Look at the log (and the output, if such is created).

In the case of a macro with parameters, test-run the macro with selected parameters.

See Maxim 4.

Re: Question regarding Macro.

Wken1122 — Fri, 26 Jan 2018 01:59:35 GMT

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.

Re: Question regarding Macro.

Tom — Fri, 26 Jan 2018 02:34:05 GMT

Try running the %LET statements without the macro wrapper and see if they do what you want.

%put &HIST_ASOF_YEAR.;
%let EFFYEAR=&HIST_ASOF_YEAR.;
%put &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.;

In general it looks like you have waaaay too many %EVAL() calls. Consider this statement.

%LET RANGE_3Y=(%EVAL(%EVAL(&YEAREFF.)-2),%EVAL(%EVAL(&YEAREFF.)-1));

Why not just do this?

%LET RANGE_3Y=(%EVAL(&YEAREFF.-2),%EVAL(&YEAREFF.-1));

So if YEAREFF is 2015 then result would be (2013,2014). Not sure why it called range 3 Y when it just covers two years.

Re: Question regarding Macro.

Kurt_Bremser — Fri, 26 Jan 2018 07:06:17 GMT

What @Tom said. Execute the %let's without the macro definition, and %put the resulting macrovar's. Then run the SQL steps one by one, to get a feel for the runtime, and inspect the individual results.

Re: Question regarding Macro.

Wken1122 — Tue, 30 Jan 2018 07:52:08 GMT

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.

Re: Question regarding Macro.

Kurt_Bremser — Tue, 30 Jan 2018 07:55:37 GMT

@Wken1122 wrote:

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.

Just look at your code, it's all there:

	%LET YEAREFF=&EFFYEAR.;
	%LET SUFF_3Y=%EVAL(&YEAREFF.)_3Y; %PUT &SUFF_3Y.;

Re: Question regarding Macro.

art297 — Tue, 30 Jan 2018 13:51:37 GMT

The code doesn't do anything that would end up in the "results" window. Everything it does would be described in the log and the result of the process would simply be the creation of one file in your work library.

Art, CEO, AnalystFinder.com