BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

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
Ksharp
Super User
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;
PaigeMiller
Diamond | Level 26
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
How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 2113 views
  • 0 likes
  • 3 in conversation