topic Re: Creating a cumulative column in SAS Programming

Creating a cumulative column

WilliamB — Tue, 10 May 2022 08:15:47 GMT

Hello, I would like to create a cumulative column in relation to the time variable "month".

Thanks for your help

			Want:
Month	Seg	Cost	Month	Seg	Cumul Cost	Cost
202201	Up	5	202201	Up	49	49
202201	Down	10	202201	Down	109	109
202202	Up	33	202201	Right	44	44
202202	Left	66	202201	left	33	33
202201	Right	44	202202	Up	115	66
202201	left	22	202202	Down	109	0
202203	left	66	202202	Right	44	0
202203	right	88	202202	Left	99	66
202201	Up	44	202203	Up	115	0
202201	Down	99	202203	Down	109	0
202202	Up	33	202203	Right	143	99
202201	left	11	202203	Left	264	165
202203	left	99
202203	right	11

Re: Creating a cumulative column

ChrisNZ — Tue, 10 May 2022 08:52:03 GMT

Why not SQL with sum(COST) and group by MONTH, SEG ?

Re: Creating a cumulative column

andreas_lds — Tue, 10 May 2022 12:01:42 GMT

Please add some details explaining the logic you want applied and post the data in usable form.

Re: Creating a cumulative column

Astounding — Tue, 10 May 2022 21:25:05 GMT

Here's one way to address the problem. Note that this is untested code, so you will have to try it to see if it works.

data test;
   array cums {202201:202203, 4} _temporary_ (0 0 0 0 0 0 0 0 0 0 0 0);
   set have end=done;
   select (upcase(seg));
      when ('UP')  col=1;
      when ('DOWN')  col=2;
      when ('RIGHT')  col=3;
      when ('LEFT')  col=4;
   end;
   cums{month, col} + cost;
   if done;
   do month = 202201 to 202203;
      cum_cost = 0;
      do col = 1 to 4;
         cost = cums{month, col};
         cum_cost + cost;
         select {col};
             when (1) seg='Up';
             when (2) seg='Down';
             when (3) seg='Right';
             when (4) seg='Left';
         end;
         output;
      end;
   end;
   drop   col;
run;
proc print;
run;

For future reference, note that "right" and "Right" are different in most any programming language. I made reasonable assumptions about what the output should contain. And if you have any values that are misspelled (such as "Rigt") the program will end with an error.