Macro for Sum by Category

[deep2110]

Hello, I am fairly new to SAS. I was working with the cars dataset from sashelp library. I want to create 38 datasets of all the "make" with a column of total sales which basically is the sum of 'invoice'. How do I write a macro for this?

Below is the code I have written to create the dataset for the 'make' Acura :

Thanks in advance!

data cars;
set sashelp.cars;
run;

proc sort data=cars;
by make;
run;

data cars2;
set cars;
by make;
if first.make then TotalInvoice = 0;
Sales + Invoice;
if last.make;
drop totalinvoice;
run;

data cars3 ;
set cars;
where make="Acura";
run;

proc sql;
create table Acura as select make,invoice,sum(cars3.invoice) as Sales
from cars3;
quit;

[BrunoMueller]

Hi

To run the same code for all the data values of a variable checkout this excellent blog Implement BY processing for your entire SAS program/ by @ChrisHemedinger

I think the processing for each make can be improved, by just using the Proc SQL at the end with the additional where clause added.

Please also note, that SAS data set names may not contain any blanks, so you have to remove the blanks from make with for instance the COMPRESS function.

Bruno

[deep2110]

The blog is really good. Thanks for sharing.

[Astounding]

First, note that CARS2 contains errors.  TOTALINVOICE is not being used, and so shouldn't even appear in the program.  If you want SALES to be the sum of the INVOICE values, you need this line:

if first.make then Sales=0;

Without it, the SALES values will be incorrect.

Given that you fix the calculations when creating CARS2, this would be an appropriate follow-up:

data _null_;

call execute('proc sql;');

do until (done);

   set cars2 end=done;

   call execute('create table ' || make || 

                ' as select make, sales from cars2 where make="' || make || "';");

end;

call execute('quit;');

stop;

run;

It's untested code, so no guarantees about whether it needs any debugging.  But note that INVOICE provides no value in the final data set.  INVOICE is merely the last value of INVOICE from the original data set for that MAKE.  SALES already exists in CARS2.  So SQL doesn't need to perform any calculations, it merely needs to copy data values.  And finally the program may not look like macro language, but CALL EXECUTE is definitely part of macro language.

[deep2110]

Thanks for pointing out that mistake. Though your code is giving errors,but it's helpful. If you can run it check what's wrong it'd be great. Meanwhile I was able to extract 38 datsets(based on Make) using

below macro:

Proc sql ;Select Distinct Make into : make separated by "|" from cars;
Quit;

%macro get ;

%do i=1 %to &sqlobs;
%let m1= %scan(&make,&i,"|");
%put Make= &m1;
Data &m1;
Set Cars;
if Make="&m1";
run;

%end;

%mend get;

%get;

Thank you!

[rogerjdeangelis]

Why you should use DOSUBL instead of a macro

( this just redirects you back to this post)
https://goo.gl/s5mqqv
https://communities.sas.com/t5/Base-SAS-Programming/Macro-for-Sum-by-Category/m-p/346050

Last posted solution fails

Splitting sashelp.cars in one dataset per car make.
I realize there are many other more efficient ways to do this(ie hash),
but that is not the point.

REASONS

 1. Can stop the submission of code if error in any iteration
 2. Encapsulates code in one address space
 3. Stored program
 4. Theoretically should run much faster(needs development work?)

HAVE meta data car makes in sashelp.cars
=======================================
   distinct values of column MAKE in SASHELP.CARS
         Acura
         Audi
         BMW
         Buick
         Cadillac
         Chevrolet
         Chrysler
         ...
         Saturn
         Scion
         Subaru
         Suzuki
         Toyota
         Volkswagen
         Volvo

WANT ( create datasets and stop code if the creation of any dataset fails)
============================================================

Solution fails for 'Mercedes-Benz' because a SAS dataset name cannot have a '-';

I want to print this message and stop;

Dataset creation failed for Mercedes-Benz  stopping parent

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;,
              _DATA_, _LAST_, _NULL_.
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MERCEDES may be incomplete.  When this step was stopped there were 0
         observations and 15 variables.
WARNING: Data set WORK.MERCEDES was not replaced because this step was stopped.
WARNING: The data set WORK.BENZ may be incomplete.  When this step was stopped there were 0
         observations and 15 variables.
WARNING: Data set WORK.BENZ was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


Dataset creation failed for Mercedes-Benz  stopping parent

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

data _null_;

  if _n_=0 then do;
     %let rc=%sysfunc(dosubl('
        Proc sql ;
        Select Distinct quote(trim(Make))
        into : makes separated by ","
        from sashelp.cars
        ;quit;
     '));
  end;

  * check that proc sql ran;
  if &rc then stop;

  length make $32;
  do make = &makes.;

     call symputx('make',make);
     rc=dosubl('
        data &make;
           set sashelp.cars(where=(make="&make"));
        run;quit;
        %let Errortext= &syserrortext;
        %let Errorcode= &syserr;
     ');

    if symget('errorcode') ne "0"  then do;
      put "dataset creation failed for " make " stopping parent";
      stop;
    end;
  end;

  stop;

run;quit;