Hello everyone,
Here is an example for which I have some difficulties:
data test;
length VAR1 $ 1; input VAR1 $ VAR2 VAR3;
datalines;
A 1 157
A 2 130
A 3 67
A 4 -54
A 5 -89
A 6 -101
B 1 500
B 2 456
B 3 234
B 4 -10
B 5 -303
B 6 -409
C 1 239
C 2 210
C 3 150
C 4 -2
C 5 -30
C 6 -44
The number of records for A, B, C etc is fixed and equal to 6.
VAR3 is sorted from high to low and contains negative values.
The steps to perform are the following:
1) For each VAR1 (A, B, C etc) identify VAR2 and VAR3 for which VAR3 is negative for the first time
2) Identify VAR2 and VAR3 of the record just above
3) Make an operation with values obtained from 1) and 2). See example below:
Example: for VAR1 = A
1) VAR2 = 4 VAR3=-54
2) VAR2 = 3 VAR3=67
3) Make an operation with 1) and 2): 4+ (4-3)*[67/(67-(-54))]
Thank you!
Welcome to the SAS Communities 🙂 If your data represents your actual problem, then do something like this
data test;
length VAR1 $ 1; input VAR1 $ VAR2 VAR3;
datalines;
A 1 157
A 2 130
A 3 67
A 4 -54
A 5 -89
A 6 -101
B 1 500
B 2 456
B 3 234
B 4 -10
B 5 -303
B 6 -409
C 1 239
C 2 210
C 3 150
C 4 -2
C 5 -30
C 6 -44
;
data temp;
merge test test(firstobs=2 rename=(VAR2=_VAR2 VAR3=_VAR3));
newvar=_VAR2+(_VAR2-VAR2)*(VAR3 / (VAR3-_VAR3));
if VAR3 gt 0 & _VAR3 lt 0;
format newvar 8.2;
run;
Welcome to the SAS Communities 🙂 If your data represents your actual problem, then do something like this
data test;
length VAR1 $ 1; input VAR1 $ VAR2 VAR3;
datalines;
A 1 157
A 2 130
A 3 67
A 4 -54
A 5 -89
A 6 -101
B 1 500
B 2 456
B 3 234
B 4 -10
B 5 -303
B 6 -409
C 1 239
C 2 210
C 3 150
C 4 -2
C 5 -30
C 6 -44
;
data temp;
merge test test(firstobs=2 rename=(VAR2=_VAR2 VAR3=_VAR3));
newvar=_VAR2+(_VAR2-VAR2)*(VAR3 / (VAR3-_VAR3));
if VAR3 gt 0 & _VAR3 lt 0;
format newvar 8.2;
run;
I like the terse 2-pass solution by @PeterClemmensen . However, it can also be done in a single pass, while avoiding unnecessary computations:
data have ;
input var1: $1. var2 var3 ;
cards ;
A 1 157
A 2 130
A 3 67
A 4 -54
A 5 -89
A 6 -101
B 1 500
B 2 456
B 3 234
B 4 -10
B 5 -303
B 6 -409
C 1 239
C 2 210
C 3 150
C 4 -2
C 5 -30
C 6 -44
run ;
data need ;
do until (last.var1) ;
set have ;
by var1 ;
if N (var) then continue ;
if var3 < 0 then do ;
var = var2 + (var2 - _var2) * divide (_var3, _var3 - var3) ;
output ;
end ;
_var2 = var2 ;
_var3 = var3 ;
end ;
run ;
Kind regards
Paul D.
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: