Solved
Contributor
Posts: 42

# 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. ?

Accepted Solutions
Highlighted
Solution
‎02-01-2018 08:45 PM
Super User
Posts: 7,934

## 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.

All Replies
PROC Star
Posts: 8,149

## 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: 9,903

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 42

## Re: Question regarding Macro.

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
‎02-01-2018 08:45 PM
Super User
Posts: 7,934

## 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: 9,903

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 42

## Re: Question regarding Macro.

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: 9,903

## 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
How to convert datasets to data steps
How to post code
PROC Star
Posts: 8,149

## 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.