Write and run SAS programs in your web browser

Question regarding Macro.

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Question regarding Macro.

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.


Accepted Solutions
Highlighted
Solution
3 weeks ago
Super User
Super User
Posts: 7,399

Re: Question regarding Macro.

[ Edited ]

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.

View solution in original post


All Replies
PROC Star
Posts: 7,800

Re: Question regarding Macro.

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

 

Super User
Posts: 8,590

Re: Question regarding Macro.

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: Question regarding Macro.

Posted in reply to KurtBremser

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.

Highlighted
Solution
3 weeks ago
Super User
Super User
Posts: 7,399

Re: Question regarding Macro.

[ Edited ]

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.

Super User
Posts: 8,590

Re: Question regarding Macro.

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: Question regarding Macro.

Posted in reply to KurtBremser

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. 

Super User
Posts: 8,590

Re: Question regarding Macro.


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.;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,800

Re: Question regarding Macro.

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 268 views
  • 5 likes
  • 4 in conversation