## calculating rate of change bw last and first vars by category

Solved
Super Contributor
Posts: 441

# calculating rate of change bw last and first vars by category

Hi,

suppose I have the following data:

datesectorprice
Jan2010112
Feb201013
Mar201016
Jun200928
Oct201024
Jan2011210

what I would like to get is the rate of return bw the last obs and the first obs for each sector. For ex, for sector 1 its = (6-12)/6 and for sector 2 its (10-8)/8.

I incorporated the following lines in my code:

data ind_per;

set ind_sect;

return = (last.price - first.price)/first.price ;

by sector;

run;

but got lots of errors and warning messages and my result wasn't what I wanted.

Thank you

Accepted Solutions
Solution
‎03-15-2015 01:38 PM
PROC Star
Posts: 1,167

## Re: calculating rate of change bw last and first vars by category

You were very close. The first. and last. variables indicate that this record is the first or last for the group, it doesn't contain the variable value. You need to keep the first value around, and then use your formula on the last record.

I added a sort, as this will return incorrect data if your input file isn't sorted correctly.

Tom

proc sort data=ind_sect;

by sector date;

run;

data ind_per;

set ind_sect;

by sector;

retain FirstPrice;

if first.sector

then FirstPrice = price;

if last.sector

then do;

return = (price - FirstPrice)/FirstPrice;

output;

end;

run;

All Replies
Solution
‎03-15-2015 01:38 PM
PROC Star
Posts: 1,167

## Re: calculating rate of change bw last and first vars by category

You were very close. The first. and last. variables indicate that this record is the first or last for the group, it doesn't contain the variable value. You need to keep the first value around, and then use your formula on the last record.

I added a sort, as this will return incorrect data if your input file isn't sorted correctly.

Tom

proc sort data=ind_sect;

by sector date;

run;

data ind_per;

set ind_sect;

by sector;

retain FirstPrice;

if first.sector

then FirstPrice = price;

if last.sector

then do;

return = (price - FirstPrice)/FirstPrice;

output;

end;

run;

Super Contributor
Posts: 441

## Re: calculating rate of change bw last and first vars by category

Hi Tom,

thanks a lot for the code it worked perfectly!!!

On the same note, may I ask you a small variation of the question:

I also wanted to find the return for each period and did the following code:

data ind_lag;

set ind_sect;

by sector;

return = (price - lag(price)) / (lag(price));

run;

The only problem is that it doesn't differentiate bw the sectors and therefore calculates the return of the first period of a sector using the last period of the previous sector, whereas the first period of each sector should be empty

thanks!

Frequent Contributor
Posts: 115

## Re: calculating rate of change bw last and first vars by category

Try:

data ind_lag;

set ind_sect;

by sector;

if not first.sector then return = (price - lag(price)) / (lag(price));

run;

or

data ind_lag;

set ind_sect;

by sector;

k=lag(price);

if not first.sector then return = (price - k) / k;

drop k;

run;

Super Contributor
Posts: 441

## Re: calculating rate of change bw last and first vars by category

Posted in reply to naveen_srini

Hi naveen, thanks for the code, it seems very intuitive but when I ran it I got the following:

 1 2 3 JAN2007 0 6698 43.5823 . FEB2007 0 6699 43.1469 . MAR2007 0 6703 43.4358 0.00669

for my sector 0 it omits the first 2 periods instead of the first one period, and for the following periods I get something like this:

 96 97 98 DEC2014 0 7008 65.515 0.02895 JAN2007 1 1 15 24.1935 . FEB2007 1 1 14 25.1778 -0.61569

JAN2007 for sector 1 is omitted as it should be, but the return for FEB2007 uses the period DEC2014 instead of JAN2007

Super Contributor
Posts: 441

## Re: calculating rate of change bw last and first vars by category

Hi again naveen,

I did the second code that you put and it worked!!!

sorry didn't see it at first, for some reason it didn't show on my email message

Super Contributor
Posts: 441

## Re: calculating rate of change bw last and first vars by category

Hi Tom,

thanks again for the code, its what I wanted!

I was just wandering, is it possible to turn your code into a macro where I actually input the first and last periods (i.e, the starting and ending dates)?

Thnaks

🔒 This topic is solved and locked.

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

Discussion stats
• 6 replies
• 620 views
• 4 likes
• 3 in conversation