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 lock in 2025 pricing—just $495!
Still thinking about your presentation idea? The submission deadline has been extended to Friday, Nov. 14, at 11:59 p.m. ET.
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.