SAS macro for adding sales figures from different monthly datasets

[Xray]

Hi,

I have several seasonal datasets with the same variables for the same divisions - e.g. Month1 dataset has a column called 'sales' for each divisionXi.

I need a SAS macro to add, say, all monthly sales for each division to get a year's total without merging the monthly datasets.

Each dataset looks like this;

Dataset Mi

Division Sales Profit Salary ...

Anyone who can help with a macro to create a yearly dataset summing monthly figures?

Ray

[Loko]

Hello,

Something like this:

data month1;
input Division Sales Profit Salary;
datalines;
1 200 10 90
2 300 20 50
;

data month2;
input Division Sales Profit Salary;
datalines;
1 300 10 90
2 400 20 50
;

%macro sums(tbl);

%let i=1;
%let salesall=0;

%do %until (%scan(&tbl, &i)=);

%let mnno=%scan(&tbl, &i);
proc sql;
select sum(sales) into :summonth&i from &mnno;
quit;

%let i=%eval(&i+1);

%end;

%do j=1 %to %eval(&i-1);
%let salesall=%eval(&salesall+&&summonth&j);
%end;

%put Total sales = &salesall;

%mend sums;

%sums(month1 month2)

[RamKumar]

May I request you to explain the below line from your code?

%do %until (%scan(&tbl, &i)=);

If I'm not wrong, &tbl will resolves to 'month' when i=1. So your code will convert to %do %until (%scan(month1, 1)=);  We don't have any variable called 'month1' in both the tables instead it's a table.

[Loko]

the loop is used to read &tbl macro variable which when calling the macro is month1 month2.

when i=1 the result of (%scan(&tbl, &i) will resolve to month1.

You can use macro options - symbolgen, mprint , mlogic to check how the macro works.

My code should be used under my assumption that data looks as per the 2 data sets (month1 and month2) provided before the macro.

[Xray]

I get the error report: "A character operand was found in the%EVAL function.... where a numeric operand is required. ..."

Some values have decimals...any solution please?

[Reeza]

Why not append the datasets? Is it because the data is too big?

I would create a view and then run a proc means on the data myself. 

[RW9]

Or, alternatively do it in straight code, no macros/variables (note that I prefer to put results into a dataset, this helps further processing so you could easily change the select into):

data month1;
  input Division Sales Profit Salary;
datalines;
1 200 10 90
2 300 20 50
;
run;

data month2;
  input Division Sales Profit Salary;
datalines;
1 300 10 90
2 400 20 50
;
run;

proc sql;
  create table RESULTS
  (
    MONTH num,
    TOTAL_SALES num
  );
quit;

data _null_;
  set sashelp.vtable (where=(libname="WORK" and substr(memname,1,5)="MONTH"));
  call execute('proc sql;
                  insert into RESULTS
                  set MONTH='||strip(tranwrd(memname,"MONTH",""))||',
                      TOTAL_SALES=(select sum(SALES) from '||strip(memname)||');
                quit;');
run;

proc sql noprint;
  select  sum(TOTAL_SALES)
  into    :SALESALL
  from    RESULTS;
quit;

%put &SALESALL.;