BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sir_Highbury
Quartz | Level 8

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

 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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Sir_Highbury
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

 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;
Sir_Highbury
Quartz | Level 8

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

Reeza
Super User

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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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