How to export a single table into multiple Excel sheets?

[Derdavos]

Hey! 

I am kinda new to the SAS Base Programming, and I just want to learn it as much as I can. Thus I did some exercises, but I got stucked in a problem. I have a huge table with full of datas about cars. I have to sort them by "make" wich means that I have to categorize the car types. There are 38 types of cars (Azura - 1, Audi - 2 ect). After It I have to export the whole table by a macro. But each type (1, 2, 3, 4 ect) should be on different sheets. And there is the problem. How to export a single table, with one macro to multiple sheets by type?

Here is the code, it might help you understand what I did:

%macro sort;
proc sort data = sashelp.cars out= test.cars;
by Make;
run;
%mend;

%macro set;
data test.cars_sorted (rename=count=uniqid); <-- make them into groups
set test.cars;
by Make;
if first.make then count+1;
else count=count;
run;
%mend;

%macro export(x);
proc export data = test.cars_sorted
outfile = "D:\&x."
dbms = XLSX replace;
sheet = "car_uniqid"; <-- It should make them into sheets like "car_1".
run;
%mend export;


%sort;
%set;
%export(cars);

Can you guys help me?

Thank you! 🙂

[Derdavos · Accepted solution]

Okay everyone! 

I found the answer. 😛

Here is the code:

%macro sort;
proc sort data = sashelp.cars out= test.cars;
by Make;
run;
%mend;

%sort;

%macro set;
data test.cars_sorted (rename=count=uniqid);
set test.cars;
by Make;
if first.make then count+1;
else count=count;
run;
%mend;

%set;

%macro multisheet (x);
proc sql noprint;
select distinct uniqid 
into :carid1 - :carid38
from test.cars_sorted;
%let type = &sqlobs;
quit;
 
%do i = 1 %to &type;
proc export data = test.cars_sorted(where=(uniqid=&&carid&i))
outfile="D:\Munka\&x."
dbms= xlsx replace;
sheet = "car_&&carid&i";
run;
%end;
%mend multisheet;

%multisheet(cars);