Retain percentage change

[PetePatel]

Hi there,

I have a dataset of groups (max of 2 weeks per group) and I would like to workout the percentage change from the latest week to the first week as shown by column change ('want').

How can I create the table below (with 'change' variable) and remove groups C and E from the final dataset (as they have only 1 week)?

See example below:

Grp	Week	Amount	Change
A	1	100
A	6	150	1.5
B	1	150
B	6	195	1.3
C	1	50
D	1	30
D	6	15	0.5
E	6	100
F	1	100
F	6	120	1.2

I have tried using a retain statement, however the the code is failing on ordering.

Many thanks,

Pete

[PeterClemmensen · Accepted solution]

data have;
input Grp $ Week Amount;
datalines;
A 1 100
A 6 150
B 1 150
B 6 195
C 1 50
D 1 30
D 6 15
E 6 100
F 1 100
F 6 120
;

data want(drop=lagamount);
   set have;
   by Grp notsorted;
   lagamount=lag1(Amount);
   if first.Grp then call missing(Change);
   else Change=Amount/lagamount;
run;

[hashman]

@PetePatel:

data have ;                        
  input Grp $ Week Amount ;        
  cards ;                          
A  1  100                          
A  6  150                          
B  1  150                          
B  6  195                          
C  1   50                          
D  1   30                          
D  6   15                          
E  6  100                          
F  1  100                          
F  6  120                          
run ;                              
                                   
data want (drop = _:) ;            
  do _q = 1 by 1 until (last.Grp) ;
    set have ;                     
    by Grp ;                       
    if _q = 1 then _amt = Amount ; 
  end ;                            
  if _q > 1 ;                      
  Change = divide (Amount, _amt) ; 
run ;                              

Paul D.

[novinosrin]

Shamelessly plagiarised from PD's one of many 🙂

data have ;                        
  input Grp $ Week Amount ;        
  cards ;                          
A  1  100                          
A  6  150                          
B  1  150                          
B  6  195                          
C  1   50                          
D  1   30                          
D  6   15                          
E  6  100                          
F  1  100                          
F  6  120                          
run ;  
data want;
do _n_=1 by 1 until(last.grp);
set have;
by grp;
Change = ifn(mod(_n_,2)=0 ,divide (Amount,_amt),.) ; 
_amt=amount;
output;
end;
drop _:;
run;