DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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;


Accepted Solutions
Solution
‎06-08-2018 08:26 AM
Super User
Posts: 10,258

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

Posted in reply to CarolineDP

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

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

Posted in reply to CarolineDP

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

 

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.

Solution
‎06-08-2018 08:26 AM
Super User
Posts: 10,258

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

Posted in reply to CarolineDP

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

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

Posted in reply to KurtBremser

Using the lag function was enough! Thank you!

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 96 views
  • 0 likes
  • 3 in conversation