Desktop productivity for business analysts and programmers

How to create a cumulative column in output data table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to create a cumulative column in output data table

Hello Everyone

 

i would like to seek your help regarding how i can add a cumulative column into output data table, current data table as below:

 

Capture.PNG

 

i want to add a colume into above table with cumulative result on "sum of count of WIS product id" base on "REP_MON",  group by "COV_ID".

 

i'm struggling regarding how to achieve this.

 

can i get your advice regarding this?

 

thank you very much in advance.


Accepted Solutions
Solution
‎02-21-2017 04:14 AM
Esteemed Advisor
Posts: 6,656

Re: How to create a cumulative column in output data table

Use by and retain

data want;
set have;
by cov_id;
retain cum_sum;
if first.cov_id then cum_sum = 0;
cum_sum + sum_of_......;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,656

Re: How to create a cumulative column in output data table

Please post your example data in a data step with datalines (so we don't have to type it off a picture), and what the expected output should look like.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: How to create a cumulative column in output data table

hi

 

here comes:

 

COV_IDREP_MONAVG_of_REP_DATESUM_of_COUNT_of_WIS_PRODUCT_IDCumulate Result
C00172016-012016-01-141818
C00172016-022016-02-132240
C00172016-032016-03-161353
C00172016-042016-04-19659
C00172016-052016-05-161776
C00172016-062016-06-12985
C00172016-072016-07-22186
C00172016-082016-08-10288
C00172016-092016-09-08189
C00172016-102016-10-15392
C00172016-112016-11-15294
C00172016-122016-12-16296
C00172017-012017-01-06197
C00182016-072016-07-2111
C00232014-082014-08-2444
C00232014-092014-09-132226
C00232014-102014-10-161440
C00232014-112014-11-111050
C00232014-122014-12-15656
C00232015-012015-01-172783
C00232015-022015-02-121396

 

as you can see, i added last colume as result i want.

 

thank you in advance.

Solution
‎02-21-2017 04:14 AM
Esteemed Advisor
Posts: 6,656

Re: How to create a cumulative column in output data table

Use by and retain

data want;
set have;
by cov_id;
retain cum_sum;
if first.cov_id then cum_sum = 0;
cum_sum + sum_of_......;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: How to create a cumulative column in output data table

hi

 

thank you very much.

 

just question as i'm really new on SAS code (normally i use graphic design mode only).

 

data want;   What is the meanning of "want" and "have"?  should i replace with other actual name?
set have;
by cov_id;
retain cum_sum;
if first.cov_id then cum_sum = 0;
cum_sum + sum_of_......;
run;

and also, should i build above code as an expression? when i choose to create a new column.

 

thank you in advance.

Esteemed Advisor
Posts: 6,656

Re: How to create a cumulative column in output data table

The dataset named in the data statement will be created; the dataset named in the set statement is the one you already have.

"have" and "want" are generic names we often use here, so you need to replace those names with your real dataset names.

The same is true for the variable names.

And what do you mean by "build as expression"?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: How to create a cumulative column in output data table

hi

 

thank you very much.

 

i figure out where to put those code in.

 

thanks again.

 

BR

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 166 views
  • 0 likes
  • 2 in conversation