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.;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.