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

Dear All,

I would like to compute stock returns from prices. I have the following dataset:

data stocks;

   input key V1 V2;

datalines;

1 0.1   10

1 0.2   15

2 0.3   25

2 0.4   30

2 0.5   33

;

where "key" is the company id and "V2" is the share price. I would like to obtain the variable V3 = V2(i)/V2(i-1) -1, but only for observations with the same value of the variable key. I would like to have a blank cell when either the V2(i-1) observation is missing or key(i) is different from key(i-1).

keyV1V2V3
10.110  .
10.2 150.5 = (15/10)-1
20.325
20.4 300.2 = (30/25)-1
20.5330.1 = (33/30)-1

My attempt involves the procedure expand. First, I was trying to obtain the variables key and V2 shifted backword by 1 (i.e., key_lead and V2_lead). Then, I was using a conditional statement to check whether two consecutive observations have the same value of the variable "key" and computing the variable V3 as V2/V2_lead -1. However, SAS returns me an error.


proc expand data=stocks out=stocks_lead method = none; 

  convert key = key_lead  / transformout=(lead 1);

  convert V2 = V2_lead  / transformout=(lead 1);

  if key_lead = key

  then V3 = V2/V2_lead - 1;

  else V3 = .;

run;

Any help would be highly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

data stocks;
input key V1 V2;
datalines;
1 0.1   10
1 0.2   15
2 0.3   25
2 0.4   30
2 0.5   33
;

data want;
set stocks;
by key v1 v2 notsorted;
v3=v2/lag(v2)-1;
if first.key then v3=.;
run;

View solution in original post

3 REPLIES 3
RaviKommuri
Fluorite | Level 6

Hi Mark,

You can try below mentioned code....

data stocks;                                     

   input key V1 V2;                              

datalines;                                       

1 0.1   10                                       

1 0.2   15                                       

2 0.3   25                                       

2 0.4   30                                       

2 0.5   33                                       

;                                                

                                                 

data want(drop=prev_key prev_v2);                

set stocks;                                     

if prev_key = key then v3 = (v2/prev_v2) - 1;   

prev_key = key;                                 

prev_v2 = v2;                                   

retain prev_key prev_v2;                       

stat_sas
Ammonite | Level 13

data stocks;
input key V1 V2;
datalines;
1 0.1   10
1 0.2   15
2 0.3   25
2 0.4   30
2 0.5   33
;

data want;
set stocks;
by key v1 v2 notsorted;
v3=v2/lag(v2)-1;
if first.key then v3=.;
run;

mark_ph
Calcite | Level 5

Thank you guys!! Both solutions work.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 3880 views
  • 4 likes
  • 3 in conversation