topic Re: Perform cummulative for each row in SAS Programming

Perform cummulative for each row

curious_guy — Sun, 06 Nov 2022 15:12:07 GMT

Hi SAS Expert.

I want to ask how to do cumulative sum on previous cell for each row.

Here is data from proc tabulate:

The output I want.

So basically the value is the sum from previous cell.

Here is the code i use to to proc tabulate

proc tabulate data = daybalance;
table year, day*balance;
var balance;
class year day;
run;

Kindly help with this if you know thank you for your help it means a lot for me.

Re: Perform cummulative for each row

ballardw — Sun, 06 Nov 2022 15:44:25 GMT

Proc Tabulate will not do what you want. You could use Proc means/summary, to get year/day sums and a data step to generate cumulative totals and then Proc tabulate or Report to display the result.

Perhaps something like:

proc summary data=daybalance nway;
   class day year ;
   var balance;
   output out = daysummary (drop=_: ) sum=;
run;

data want;
   set daysummary;
   by day;
   retain cumbalance;
   if first.day then cumbalance=balance;
   else cumbalance + balance;
run;

And then use the Want set in proc tabulate to display values of Cumbalance created above.

Caution: If you include any ALL the result may not be what you expect depending on the dimension and statistic used with cumulative variables.

You could use an output set created from Proc Tabulate to feed into the data step but the structure of those sets may be a bit more complicated to work with.

For actual working code you would have to show an example of your starting data in the form of a data step.

Re: Perform cummulative for each row

Tom — Mon, 07 Nov 2022 02:49:11 GMT

Assuming that DAY is character then you could just do it yourself.

/*
proc tabulate data = daybalance;
table year, day*balance;
var balance;
class year day;
run;
*/

proc sql;
create table summary as 
select year
     , sum(case when day in ('Sunday') then balance else 0 end) as Sunday
     , sum(case when day in ('Sunday','Monday') then balance else 0 end) as Monday
     , sum(case when day in ('Sunday','Monday','Tuesday') then balance else 0 end) as Tuesday
     , sum(case when day in ('Sunday','Monday','Tuesday','Wednesday') then balance else 0 end) as Wednesday
     , sum(case when day in ('Sunday','Monday','Tuesday','Wednesday','Thursday') then balance else 0 end) as Thursday
from daybalance
group by year
;
quit;

proc print;
run;

Re: Perform cummulative for each row

Ksharp — Mon, 07 Nov 2022 11:53:45 GMT

/*
You need multi-label format
*/


data have;
do year=2016 to 2017;
 do weekday=1 to 4;
   balance=1;
   output;
 end;
end;
run;

proc format;
value fmt(multilabel)
1='Monday'
1-2='Tuesday'
1-3='Wednesday'
1-4='Thursday'
;
run;


proc tabulate data=have format=32.0;
format weekday fmt.;
class year ;
class weekday/mlf preloadfmt  order=data;
var balance;
table year,weekday*balance;
run;

Re: Perform cummulative for each row

curious_guy — Wed, 09 Nov 2022 01:46:59 GMT

i there thank you for your solutions. It works!

Re: Perform cummulative for each row

curious_guy — Wed, 09 Nov 2022 01:47:27 GMT

Thank youu for your support