Dear Experts,
I am in the following situation (attached file):
I have several produts (row) and and different variables corresponding to different monthly date, e.g. XX_201501, XX_201502, XX_201503... XX_201610 (progressiv date).
How can I sum by row from 201503 to 201506? Thanks and best regards.
The code I gave should be a good starting point:
data want; set have; array dts{8} 201501--201508; /* Note I use the shorthand --, all variables between */ result=0; do i=3 to 7; result=sum(results,dts{i}); end; run;
Basically you set and array reference to your variables, then loop over then. Or you could just do it directly:
data want; set have; result=sum(201503,201504,201505,201506); run;
Hi,
To get tested code, its a good idea to post test data in the form of a datastep in the post - Excel files are a dangerous download and a pain to deal with at any point.
My first suggestion would be to normalise your data, unless you are creating a report - i.e. at the proc report stage, you will find your programming is a lot easy to write and maintain if your data is long, rather than wide. For instance this problem would become a simple select sum() from have where date between "20150301"d and "20150601"d. To do the same thing in this data structure you need to put in arrays, and this doesn't carry over differet steps. The syntax is (untested due to the above):
data want; set have; array dts{8} 201501--201508; /* Note I use the shorthand --, all variables between */ result=0; do i=3 to 7; result=sum(results,dts{i}); end; run;
dear RW9,
thanks for the suggestions and the prompt reply. I also thought using the proc transpose, but the output I should generate is exactly as the input, just aggregating some monthts on the base of the sales period. Can I do the calculation sticking to the same structure (just having one more column with the sum).
Thanks again.
The code I gave should be a good starting point:
data want; set have; array dts{8} 201501--201508; /* Note I use the shorthand --, all variables between */ result=0; do i=3 to 7; result=sum(results,dts{i}); end; run;
Basically you set and array reference to your variables, then loop over then. Or you could just do it directly:
data want; set have; result=sum(201503,201504,201505,201506); run;
data top_6;
set Perm.Summary_sales;
test=sum(xx_200906--xx_200909);
run;
it seems to be also ok.... or? Thanks for the hint (--)
Usually you should use the keyword OF in the sum function if you're using a list of variables
ie.
sum(OFStart_var--end_var)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.