calculate variable by looping specifics rows

[mazouz]

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	-	-

[PeterClemmensen]

Is this for reporting purposes or do you want a SAS data set like this?

[mazouz]

I wan't solution by using step data or proc sql

[PeterClemmensen]

Is your data actually structured like this? 3 obs for each id?

[mazouz]

Yes the number of obs in each id is constant

[PeterClemmensen · Accepted solution]

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            .      . 

[mazouz]

Thank you Draycut

[PeterClemmensen]

Anytime

[Kurt_Bremser]

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.

[mazouz]

thank you  @Kurt_Bremser

your solution also works