topic Sum observations looping on the variables in SAS Programming

Sum observations looping on the variables

Sir_Highbury — Mon, 11 Apr 2016 13:44:10 GMT

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.

Re: Sum observations looping on the variables

RW9 — Mon, 11 Apr 2016 13:45:33 GMT

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;

Re: Sum observations looping on the variables

Sir_Highbury — Mon, 11 Apr 2016 13:57:21 GMT

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.

Re: Sum observations looping on the variables

RW9 — Mon, 11 Apr 2016 14:14:11 GMT

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;

Re: Sum observations looping on the variables

Sir_Highbury — Mon, 11 Apr 2016 14:24:12 GMT

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

Re: Sum observations looping on the variables

Reeza — Mon, 11 Apr 2016 14:36:08 GMT

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)