calculating rate of change bw last and first vars by category

[ilikesas]

Hi,

suppose I have the following data:

date	sector	price
Jan2010	1	12
Feb2010	1	3
Mar2010	1	6
Jun2009	2	8
Oct2010	2	4
Jan2011	2	10

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

[TomKari · Accepted solution]

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;

[ilikesas]

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!

[naveen_srini]

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;

[ilikesas]

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

1	JAN2007		0	6698	43.58231	.
2	FEB2007		0	6699	43.14695	.
3	MAR2007		0	6703	43.43580	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	DEC2014		0	7008	65.51498	0.02895
97	JAN2007	1	1	15	24.19350	.
98	FEB2007	1	1	14	25.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

[ilikesas]

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

[ilikesas]

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