My input dataset (Table 1) has 2 columns and 4 rows. I need to calculate row 5 and row 6 as shows in (table 2). Please help me create Row5 and Row6.
Calculation:
Row 5 - Average of row 2, 3, 4
Row 6 - (Row1 - Row5)/Row5
Table 1: (INPUT DATASET)
| WEEK | Units |
| Week 1 | 81,855 |
| Week 2 | 80,009 |
| Week 3 | 71,972 |
| Week 4 | 68,035 |
Table 2: (OUTPUT DATASET)
| WEEK | Units |
| Week 1 | 81,855 |
| Week 2 | 80,009 |
| Week 3 | 71,972 |
| Week 4 | 68,035 |
| Average (Week 2 - Week 4) | 73,339 |
| Variance | 12% |
data have;
infile cards expandtabs;
input WEEK $ Units : comma12.;
format units comma12.;
cards;
Week1 81,855
Week2 80,009
Week3 71,972
Week4 68,035
;
run;
data want;
set have end=last;
lag=lag(units);
lag2=lag2(units);
lag3=lag3(units);
char_units=put(units,comma12. -l);
output;
if last then do;
week='Average'; units=mean(lag,lag2,units);char_units=put(units,comma12. -l);output;
week='Variance';units=(lag3-units)/units;char_units=put(units,percent8.2 -l);output;
end;
run;
data have;
input text $ & units :comma6.0;
cards;
Week 1 81,855
Week 2 80,009
Week 3 71,972
Week 4 68,035
;
data want;
set have end=eof;
retain row1 average;
if _n_=1 then row1=units;
if 2<=_n_<=4 then sum+units;
if _n_<4 then output;
else if _n_=4 then do;
output;
text='Average';
units=sum/3;
average=units;
output;
text='Variance';
units=(row1-average)/row1;
output;
end;
keep text units;
run;
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and save with the early bird rate—just $795!
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.
Ready to level-up your skills? Choose your own adventure.