DATA Step, Macro, Functions and more

SAS macro for adding sales figures from different monthly datasets

Reply
New Contributor
Posts: 2

SAS macro for adding sales figures from different monthly datasets

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

Super Contributor
Posts: 308

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

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)

Regular Contributor
Posts: 168

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

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.



Super Contributor
Posts: 308

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

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.

New Contributor
Posts: 2

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

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?

Super User
Posts: 19,832

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

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. 

Super User
Super User
Posts: 7,977

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

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    Smiley FrustratedALESALL
  from    RESULTS;
quit;

%put &SALESALL.;

Ask a Question
Discussion stats
  • 6 replies
  • 344 views
  • 6 likes
  • 5 in conversation