topic Re: Calculation across rows in SAS Programming

Calculation across rows

helloSAS — Sun, 07 Mar 2021 12:31:11 GMT

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%

Re: Calculation across rows

Ksharp — Sun, 07 Mar 2021 12:54:35 GMT

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;

Re: Calculation across rows

PaigeMiller — Sun, 07 Mar 2021 13:05:08 GMT

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;