BookmarkSubscribeRSS Feed
Xray
Calcite | Level 5

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

6 REPLIES 6
Loko
Barite | Level 11

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
Fluorite | Level 6

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
Barite | Level 11

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
Calcite | Level 5

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1125 views
  • 6 likes
  • 5 in conversation