03-31-2017 02:46 AM
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!
proc sort data=cars;
if first.make then TotalInvoice = 0;
Sales + Invoice;
data cars3 ;
create table Acura as select make,invoice,sum(cars3.invoice) as Sales
03-31-2017 03:55 AM
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.
03-31-2017 07:19 AM - edited 03-31-2017 07:20 AM
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:
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 || "';");
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.
03-31-2017 07:56 AM - edited 03-31-2017 07:58 AM
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
Proc sql ;Select Distinct Make into : make separated by "|" from cars;
%macro get ;
%do i=1 %to &sqlobs;
%let m1= %scan(&make,&i,"|");
%put Make= &m1;
03-31-2017 09:53 AM
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;