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 |
Please show the forum
Then you will get a better level of help.
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.
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?
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 |
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??
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.:-)
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.
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 .
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: