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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.