BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Derdavos
Obsidian | Level 7

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! 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Derdavos
Obsidian | Level 7

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

 

View solution in original post

1 REPLY 1
Derdavos
Obsidian | Level 7

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

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 2724 views
  • 0 likes
  • 1 in conversation