topic Re: calculate variable by looping specifics rows in SAS Programming

calculate variable by looping specifics rows

mazouz — Thu, 30 Jul 2020 14:11:02 GMT

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

Re: calculate variable by looping specifics rows

PeterClemmensen — Thu, 30 Jul 2020 14:11:20 GMT

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

Re: calculate variable by looping specifics rows

mazouz — Thu, 30 Jul 2020 14:13:03 GMT

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

Re: calculate variable by looping specifics rows

PeterClemmensen — Thu, 30 Jul 2020 14:14:43 GMT

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

Re: calculate variable by looping specifics rows

Kurt_Bremser — Thu, 30 Jul 2020 14:19:48 GMT

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.

Re: calculate variable by looping specifics rows

mazouz — Thu, 30 Jul 2020 14:19:58 GMT

Yes the number of obs in each id is constant

Re: calculate variable by looping specifics rows

PeterClemmensen — Thu, 30 Jul 2020 14:29:01 GMT

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

Re: calculate variable by looping specifics rows

mazouz — Thu, 30 Jul 2020 15:49:21 GMT

thank you @Kurt_Bremser

your solution also works

Re: calculate variable by looping specifics rows

mazouz — Thu, 30 Jul 2020 15:50:43 GMT

Thank you Draycut

Re: calculate variable by looping specifics rows

PeterClemmensen — Thu, 30 Jul 2020 17:31:34 GMT

Anytime