topic Re: How to I use a macro to iteratively sum over different rows? in SAS Programming

How to I use a macro to iteratively sum over different rows?

CarolineDP — Fri, 08 Jun 2018 08:24:12 GMT

Hello,

I have the following dataset

var 1 var2 var3 var4

region 1 2000 num1 num2

region1 2001 num3 num4

region1 32002 num5 num6

...

region1 2018 num19 num20

region2 2000 num21 num22

region2 ...

regionn...

I need to create a new variable for each region for each year which computes the sum of var3 for year i + var4 for year (i+1)

Since I have 100 regions and periods, I want a macro...

I tried to create dummy variables for each year but I don't know how to invoke the dummy for year(i+1)

and I don't know what to do with my dummies...

%macro EWD;
%DO i=1950 %TO 2016;
%put bin%eval(&i+1);
sum=var3*bin&i+var4*bin%eval(&i+1));
%end;
%mend EWD_2;

data new;
set essi;
%EWD_2;
run;

Re: How to I use a macro to iteratively sum over different rows?

RW9 — Fri, 08 Jun 2018 08:37:54 GMT

Sorry your post is very hard to follow. Post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

We only need a few rows that we can run and then have something to code against. Then show what the output should look like.

What I will say off the bat is this, Macro is never needed. Almost all the macro coding I see can be replaced by simply understanding the data and how to model it correctly. It sounds in this instance, you will be able to do what you want by either using retained variables, or by using proc summary or one of the procedures designed for the purpose, but without some clear example its hard to say.

Re: How to I use a macro to iteratively sum over different rows?

Kurt_Bremser — Fri, 08 Jun 2018 08:40:13 GMT

You don't need a macro at all. Macros create dynamic code and are not for handling data.

Either sort by region and descending year, and use the lag() function, or merge the dataset to itself with firstobs=2, thereby creating a lookahead.

For code, please post data in a datastep; see my footnotes for help.

Re: How to I use a macro to iteratively sum over different rows?

CarolineDP — Fri, 08 Jun 2018 12:26:25 GMT

Using the lag function was enough! Thank you!