topic Re: SAS macro for adding sales figures from different monthly datasets in SAS Programming

SAS macro for adding sales figures from different monthly datasets

Xray — Wed, 24 Sep 2014 07:07:36 GMT

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

Re: SAS macro for adding sales figures from different monthly datasets

Loko — Wed, 24 Sep 2014 07:57:52 GMT

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)

Re: SAS macro for adding sales figures from different monthly datasets

RW9 — Wed, 24 Sep 2014 08:25:28 GMT

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.;

Re: SAS macro for adding sales figures from different monthly datasets

RamKumar — Wed, 24 Sep 2014 13:41:28 GMT

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.

Re: SAS macro for adding sales figures from different monthly datasets

Loko — Wed, 24 Sep 2014 13:52:20 GMT

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.

Re: SAS macro for adding sales figures from different monthly datasets

Xray — Wed, 24 Sep 2014 14:36:03 GMT

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?

Re: SAS macro for adding sales figures from different monthly datasets

Reeza — Wed, 24 Sep 2014 14:44:45 GMT

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.