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