topic Re: calculating rate of change bw last and first vars by category in SAS Data Management

calculating rate of change bw last and first vars by category

ilikesas — Sun, 15 Mar 2015 17:29:05 GMT

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

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

TomKari — Sun, 15 Mar 2015 17:38:52 GMT

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;

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

ilikesas — Sun, 15 Mar 2015 20:53:55 GMT

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!

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

naveen_srini — Sun, 15 Mar 2015 21:02:35 GMT

Try:

data ind_lag;

set ind_sect;

by sector;

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

run;

data ind_lag;

set ind_sect;

by sector;

k=lag(price);

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

drop k;

run;

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

ilikesas — Sun, 15 Mar 2015 21:14:28 GMT

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

1	JAN2007	6698	43.58231	.
2	FEB2007	6699	43.14695	.
3	MAR2007	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

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

ilikesas — Sun, 15 Mar 2015 21:19:50 GMT

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

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

ilikesas — Tue, 17 Mar 2015 02:26:27 GMT

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