Hello, I wan't to calculate RATIO1 and RATIO2
PRODUCT | QUANTILE | VALUE_QUANTILE | RATIO1 | RATIO2 |
P1 | 5% | 24 | (60-24)/24 | (300-60)/60 |
P1 | 50% | 60 | - | - |
P1 | 95% | 300 | - | - |
P2 | 5% | 1 | (400-1)/1 | (500-400)/400 |
P2 | 50% | 400 | - | - |
P2 | 95% | 500 | - | - |
P3 | 5% | 300 | (450-300)/300 | (600-450)/450 |
P3 | 50% | 450 | - | - |
P3 | 95% | 600 | - | - |
Ok. Then do something like this
data have;
input PRODUCT $ 1-2 QUANTILE $ 4-6 VALUE_QUANTILE;
datalines;
P1 5% 24
P1 50% 60
P1 95% 300
P2 5% 1
P2 50% 400
P2 95% 500
P3 5% 300
P3 50% 450
P3 95% 600
;
data want(drop=v2 v3);
merge have
have(firstobs=2 rename=VALUE_QUANTILE=v2 keep=VALUE_QUANTILE)
have(firstobs=3 rename=VALUE_QUANTILE=v3 keep=VALUE_QUANTILE);
if quantile = '5%' then do;
ratio1 = (v2 - VALUE_QUANTILE) / VALUE_QUANTILE;
ratio2 = (v3 - v2) / v2;
end;
run;
Result:
PRODUCT QUANTILE VALUE_QUANTILE ratio1 ratio2 P1 5% 24 1.5 4.00000 P1 50% 60 . . P1 95% 300 . . P2 5% 1 399.0 0.25000 P2 50% 400 . . P2 95% 500 . . P3 5% 300 0.5 0.33333 P3 50% 450 . . P3 95% 600 . .
Is this for reporting purposes or do you want a SAS data set like this?
I wan't solution by using step data or proc sql
Is your data actually structured like this? 3 obs for each id?
Yes the number of obs in each id is constant
Ok. Then do something like this
data have;
input PRODUCT $ 1-2 QUANTILE $ 4-6 VALUE_QUANTILE;
datalines;
P1 5% 24
P1 50% 60
P1 95% 300
P2 5% 1
P2 50% 400
P2 95% 500
P3 5% 300
P3 50% 450
P3 95% 600
;
data want(drop=v2 v3);
merge have
have(firstobs=2 rename=VALUE_QUANTILE=v2 keep=VALUE_QUANTILE)
have(firstobs=3 rename=VALUE_QUANTILE=v3 keep=VALUE_QUANTILE);
if quantile = '5%' then do;
ratio1 = (v2 - VALUE_QUANTILE) / VALUE_QUANTILE;
ratio2 = (v3 - v2) / v2;
end;
run;
Result:
PRODUCT QUANTILE VALUE_QUANTILE ratio1 ratio2 P1 5% 24 1.5 4.00000 P1 50% 60 . . P1 95% 300 . . P2 5% 1 399.0 0.25000 P2 50% 400 . . P2 95% 500 . . P3 5% 300 0.5 0.33333 P3 50% 450 . . P3 95% 600 . .
Anytime
data want;
count = 0;
do until (last.product);
set have;
by product;
count + 1;
if count = 2 then secval = value_quantile;
end;
lastval = value_quantile;
do until (last.product);
set have;
by product;
if first.product then do;
ratio1 = (secval - value_quantile) / value_quantile;
ratio2 = (lastval - secval) / secval;
end;
else do;
ratio1 = .;
ratio2 = .;
end;
output;
end;
drop count secval lastval;
run;
Untested, for lack of usable data; please post example data in a data step with datalines.
thank you @Kurt_Bremser
your solution also works
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.