Hi,
I'm trying to calculate new variables based on other variables from different row of specific value.
For example:
my_data:
name string value
name | string | value |
aba | aaaaa | 12 |
baba | bbbbb | 84 |
caca | cccc | 65 |
gaga | ddddd | 52 |
wanted_data
name | string | value | diff |
aba | aaaaa | 12 | |
baba | bbbbb | 84 | 72 |
caca | cccc | 65 | 44 |
gaga | ddddd | 52 | 40 |
The calculation of the new variable diff:
0 |
84-12 |
65-12 |
52-12 |
Please advice,
Thank you!
data have;
input name $ string $ value;
datalines;
aba aaaaa 12
baba bbbbb 84
caca cccc 65
gaga ddddd 52
;
data want(drop=v);
set have;
if _N_=1 then v=value;
diff=value-v;
retain v;
run;
data have;
input name$ string$ value;
cards;
aba aaaaa 12
baba bbbbb 84
caca cccc 65
gaga ddddd 52
;
data want;
set have;
if _n_=1 then do; t=value;diff=0;end;
else diff=value-t;
retain t;
drop t;
run;
@efi88 Also, you could use a conditional set to mimic retain
data have;
input name$ string$ value;
cards;
aba aaaaa 12
baba bbbbb 84
caca cccc 65
gaga ddddd 52
;
data want;
set have;
if _n_=1 then set have(keep=value rename=(value=t));
diff=value-t;
drop t;
run;
efi88: As has already been stated, the retain statement should be a big help.
However, it appears that you have an error in your 'wanted data'. For name 'caca', according to your specified formula, the result should be 53 (65-12), not 44 as shown.
Best Wishes.
data have;
input name $ string $ value;
datalines;
aba aaaaa 12
baba bbbbb 84
caca cccc 65
gaga ddddd 52
;
data want(drop=v);
set have;
if _N_=1 then v=value;
diff=value-v;
retain v;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.