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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 2322 views
  • 0 likes
  • 1 in conversation