CODEWITHSUNDEEP
sassumtranspose
Khalid
Khalid

Reputation: 661

SAS cumulative sum of transposed data

I want to ask a question about cumulative sum. I think I can explain better by using the simple sample. So, I have the following dataset:

 Group       Time                Value
 A               14:25                5 
 A               14:25                10
 A               14:26                 8
 B               14:25                 4
 B               14:25                 7
 C               14:25                12
 C               14:25                 3
 C               14:26                 6
 C               14:26                 9

I want to get the following datasets:

Time         A                  B                    C
14:25       15 (5+10)           11 (4+7)          15(12+3)
14:26        8                  0                 15(6+9)

Upvotes: 1

Views: 569

Answers (1)

zuluk
zuluk

Reputation: 1577

Preparing data:

data a;
  time ="14:25";
  group="A";
  value=5;
  output;
  time ="14:25";
  group="A";
  value=10;
  output;
  time ="14:26";
  group="A";
  value=8;
  output;
  time ="14:25";
  group="B";
  value=4;
  output;
  time ="14:25";
  group="B";
  value=7;
  output;
  time ="14:25";
  group="C";
  value=12;
  output;
  time ="14:25";
  group="C";
  value=3;
  output;
  time ="14:26";
  group="C";
  value=6;
  output;
  time ="14:26";
  group="C";
  value=9;
  output;
run;

Transpose data:

options missing=0;
proc sql noprint;
  CREATE TABLE a2 AS
  SELECT time
    ,group
    ,sum(value) as value
  FROM a
  GROUP BY time,group
  ;
quit;
proc transpose data=a2;
  by time;
  id group;
  var value;
run;

Instead of using proc sql you can also use proc summary:

proc summary data=a sum nonobs noprint nway;
  class time group;
  var value;
  output out=a2(drop=_type_ _freq_) SUM()=;
run;

Upvotes: 1

Related Questions