DATA Step, Macro, Functions and more

Sum observations looping on the variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

Sum observations looping on the variables

[ Edited ]

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. 

 

 


Accepted Solutions
Solution
‎04-11-2016 10:24 AM
Super User
Super User
Posts: 7,430

Re: Sum observations looping on the variables

 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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,430

Re: Sum observations looping on the variables

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;
Frequent Contributor
Posts: 127

Re: Sum observations looping on the variables

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.

Solution
‎04-11-2016 10:24 AM
Super User
Super User
Posts: 7,430

Re: Sum observations looping on the variables

 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;
Frequent Contributor
Posts: 127

Re: Sum observations looping on the variables

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 (--)

Super User
Posts: 17,942

Re: Sum observations looping on the variables

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)
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 321 views
  • 0 likes
  • 3 in conversation