BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

6 REPLIES 6
TomKari
Onyx | Level 15

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
Barite | Level 11

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
Quartz | Level 8

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
Barite | Level 11

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

ilikesas
Barite | Level 11

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
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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