topic Calculating cumulative sum in SAS Programming

Calculating cumulative sum

ndamo — Thu, 01 Sep 2022 15:35:18 GMT

I am facing cumulative sum calculation. In my dataset, the columns to be summed are something like Cycle_1 to Cycle_65.
My program works to calculate the cumulative from cycle_1 to cycle_9. However, something is wrong from cycle_10.
In my previous dataset, the column VISIT is a character type .
Below my program:
Data Test2;
SET Test1;
BY ID _VISIT_;
ATTRIB VISIT LABEL = "Visit" FORMAT = $50. ;
ATTRIB CUMUL LABEL = "Cumulative sum by cycle" FORMAT = BEST12. ;
VISIT = SUBSTR(_VISIT_,1,8) ;
IF FIRST.ID THEN CUMUL = VALUE ;
ELSE CUMUL + VALUE ;
DROP VALUE;
RUN ;

When the program , run , I have an output below.

So I need your help because I did not found were's the issues.

Many thanks

Re: Calculating cumulative sum

Tom — Thu, 01 Sep 2022 15:43:58 GMT

The code you posted does not appear to have anything to do with the variables in the photograph you attached.

Is the photograph the input data? Where is the ID and _VISIT_ and VALUE variables the code is using?

Please post input data as TEXT , preferable as a working SAS data step.

Show the desired output data as well. Preferable in the same way.

Re: Calculating cumulative sum

ndamo — Thu, 01 Sep 2022 16:26:07 GMT

Hi @Tom

Yes because in my dataset Test1 I have someting like :

ID	_VISIT_	VALUE
NNN-NNN	Cycle 1 Treatment Administration	525
NNN-NNN	Cycle 10 Treatment Administration	525
NNN-NNN	Cycle 2 Treatment Administration	525
NNN-NNN	Cycle 3 Treatment Administration	275
NNN-NNN	Cycle 4 Treatment Administration	525
NNN-NNN	Cycle 5 Treatment Administration	525
NNN-NNN	Cycle 6 Treatment Administration	525
NNN-NNN	Cycle 7 Treatment Administration	525
NNN-NNN	Cycle 8 Treatment Administration	525
NNN-NNN	Cycle 9 Treatment Administration	525

In my dataset Test2 , I have created column visit to get only the text "cycle" and its number . Maybe the SUBSTR function was not correctly worked.

Re: Calculating cumulative sum

Tom — Thu, 01 Sep 2022 16:30:15 GMT

Perhaps your issue is you created a CHARACTER visit variable with left aligned digit strings so that 10 sorts before 2 and 9.

Either right align the values so they sort properly or convert them into a number.

data step1;
  set have;
  visit = input(scan(_visit_,2, ' '),32.);
run;

proc sort data=step1;
  by id visit;
run;

data want;
  set step1;
  by id visit;
  if first.visit them cumm = value;
  else comm + value;
run;

Re: Calculating cumulative sum

ndamo — Thu, 01 Sep 2022 17:05:03 GMT

@Tom

Thank you so much. It works.

I need just to add something at level of Proc transpose .

I would like to cycle_1 ......cycle_10 .... rather than _1, 2_ ........_10

Again , thank you 🙂