topic Re: How to create a cumulative column in output data table in SAS Enterprise Guide

How to create a cumulative column in output data table

xiaoyao026 — Tue, 21 Feb 2017 07:05:22 GMT

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:

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.

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

Kurt_Bremser — Tue, 21 Feb 2017 07:24:04 GMT

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.

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

xiaoyao026 — Tue, 21 Feb 2017 07:36:34 GMT

here comes:

COV_ID	REP_MON	AVG_of_REP_DATE	SUM_of_COUNT_of_WIS_PRODUCT_ID	Cumulate Result
C0017	2016-01	2016-01-14	18	18
C0017	2016-02	2016-02-13	22	40
C0017	2016-03	2016-03-16	13	53
C0017	2016-04	2016-04-19	6	59
C0017	2016-05	2016-05-16	17	76
C0017	2016-06	2016-06-12	9	85
C0017	2016-07	2016-07-22	1	86
C0017	2016-08	2016-08-10	2	88
C0017	2016-09	2016-09-08	1	89
C0017	2016-10	2016-10-15	3	92
C0017	2016-11	2016-11-15	2	94
C0017	2016-12	2016-12-16	2	96
C0017	2017-01	2017-01-06	1	97
C0018	2016-07	2016-07-21	1	1
C0023	2014-08	2014-08-24	4	4
C0023	2014-09	2014-09-13	22	26
C0023	2014-10	2014-10-16	14	40
C0023	2014-11	2014-11-11	10	50
C0023	2014-12	2014-12-15	6	56
C0023	2015-01	2015-01-17	27	83
C0023	2015-02	2015-02-12	13	96

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

thank you in advance.

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

Kurt_Bremser — Tue, 21 Feb 2017 07:44:00 GMT

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;

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

xiaoyao026 — Tue, 21 Feb 2017 08:16:04 GMT

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.

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

Kurt_Bremser — Tue, 21 Feb 2017 08:23:56 GMT

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"?

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

xiaoyao026 — Tue, 21 Feb 2017 09:15:02 GMT

thank you very much.

i figure out where to put those code in.

thanks again.