topic Re: Cummulative Totals in SAS Programming

Cummulative Totals

Biscuitmuncher — Sun, 07 Oct 2018 07:11:16 GMT

Hi,

Hopefully somebody may be able to give me a steer as I’m fairly new to base SAS. I’m preparing a dataset for import to VA (version 7.3).I have the following dataset following a transpose procedure. I am trying to construct a cumulative total for each product by its stage (A,R and U), its status, area and town. I had thought of using by variables in a data step would be the way forward but I have not been able to get it to work. Does anybody have any suggestions please?

Dayno	Product	Status	Area	Town	Stage A	StageR	StageU	Daily_Tot
0	DGSEPCHI	E	HWB	NMSW	2	2	.	4
0	DGSEPCHI	H	HWB	NMSW	23	18	4	45
0	DGSEPCHI	O	HWB	APS	2	2	.	4
0	DGLW4	H	SASS	CJC	4	5	5	14
12	DGSEPCHI	H	HWB	NMSW	.	.	.	0
27	DGLW4	H	SASS	CJC	1	4	.	5
39	DGLW4	H	SASS	CJC	.	.	.	0
40	DGLW4	H	SASS	CJC	1	6	2	9
41	DGLW4	H	SASS	CJC	.	.	6	6
43	DGLW4	H	SASS	CJC	1	.	.	1
47	DGLW4	H	SASS	CJC	.	.	.	0
48	DGSEPCHI	H	HWB	APS	1	.	.	1

Re: Cummulative Totals

mkeintz — Sun, 07 Oct 2018 10:58:04 GMT

Please show the forum

What the output with cumulative totals should look like,
The program with BY statements that you tried, along with the results

Then you will get a better level of help.

Re: Cummulative Totals

PaigeMiller — Sun, 07 Oct 2018 11:25:10 GMT

I am trying to construct a cumulative total for each product by its stage (A,R and U), its status, area and town.

Cumulative total of which variable?

Re: Cummulative Totals

Reeza — Sun, 07 Oct 2018 17:15:48 GMT

It may be better to do this in VA directly as a calculated column if you want an interactive variable that adjusts based on filters and such.

This is for median, but can probably be used for a SUM as well.

https://stackoverflow.com/questions/50334174/how-do-i-create-a-cumulative-median-field-in-sas-va-7-4-designer

Re: Cummulative Totals

Biscuitmuncher — Sun, 07 Oct 2018 19:03:33 GMT

Hi,

Firstly thanks for all the replies to date. I am not sure that SAS VA version 7.3. would allow a daily cumulative total, but I will have a look. The link is very helpful .

Re: Cummulative Totals

Biscuitmuncher — Sun, 07 Oct 2018 19:33:37 GMT

Hi,

Thank you so much for the response. Essentially I am trying to obtain a cumulative total for each product by area, town and status. Something along the lines of the attached. Any steer you can give will be great. Thank you.

Dayno	Product	Status	Area	Town	Stage A	StageR	StageU	Cum Area _Stage A	Cum Town_ Stage A	Cum Status Stage A
0	DGSEPCHI	E	HWB	NMSW	2	2	.	2	2	2
0	DGSEPCHI	H	HWB	NMSW	23	18	4	25	25	23
0	DGSEPCHI	O	HWB	APS	2	2	.	27	2	2
0	DGLW4	H	SASS	CJC	4	5	5	4	4	4
12	DGSEPCHI	H	HWB	NMSW	.	.	.	27	25	23
27	DGLW4	H	SASS	CJC	1	4	.	5	5	5
39	DGLW4	H	SASS	CJC	.	.	.	5	5	5
40	DGLW4	H	SASS	CJC	1	6	2	6	6	6
41	DGLW4	H	SASS	CJC	.	.	6	6	6	6
43	DGLW4	H	SASS	CJC	1	.	.	7	7	7
47	DGLW4	H	SASS	CJC	.	.	.	7	7	7
48	DGSEPCHI	H	HWB	APS	1	.	.	8	8	3

Re: Cummulative Totals

Biscuitmuncher — Sun, 07 Oct 2018 19:35:28 GMT

Thank you for responding. I have included the output I am trying to achieve earlier in the thread. Any thoughts and ideas would be great.

Re: Cummulative Totals

PaigeMiller — Sun, 07 Oct 2018 20:02:55 GMT

Thank you so much for the response. Essentially I am trying to obtain a cumulative total for each product by area, town and status. Something along the lines of the attached. Any steer you can give will be great. Thank you.

I'm still struggling with your wording. It seems you want a cumulative total for each product and area, and then another cumulative total for each product and town, and then a third cumulative total by each product and status. Is that what you want?

Typically, to compute cumulative totals, you have to first sort the data into the groupings of interest, in order to compute cumulative totals.

If so, you would need to do a sort by product and area and compute cumulative totals for that case. Then do another sort by product and town, and compute cumulative totals for that case. Then do another sort by product and status, and compute cumulative totals for that case.

Furthermore, in order for cumulative totals to be correct, you need to have the correct ordering withing your groups. You haven't mentioned the variable DAYNO, is that the proper ordering of the data within the groups??

Re: Cummulative Totals

Biscuitmuncher — Sun, 07 Oct 2018 20:48:06 GMT

Hi,

Apologies. Essentially you are correct. I am indeed after a cumulative total over a period of days, for a.) product and area, product and town, product and status.

I will give the sort procedures a go.

Thankyou.:-)