Obsidian | Level 7

## Calculation across rows

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%
2 REPLIES 2
Super User

## Re: Calculation across rows

``````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;``````
Diamond | Level 26

## Re: Calculation across rows

``````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;``````
--
Paige Miller
Discussion stats
• 2 replies
• 1478 views
• 0 likes
• 3 in conversation