SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

calculating rate of change bw last and first vars by category

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

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;

View solution in original post


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:

1JAN2007 0669843.58231.
2FEB2007 0669943.14695.
3MAR2007 0670343.435800.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:

96DEC2014 0700865.514980.02895
97JAN2007111524.19350.
98FEB2007111425.17784-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