DATA Step, Macro, Functions and more

How to export a single table into multiple Excel sheets?

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

How to export a single table into multiple Excel sheets?

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! Smiley Happy


Accepted Solutions
Solution
‎03-22-2017 05:15 AM
Contributor
Posts: 21

Re: How to export a single table into multiple Excel sheets?

Okay everyone! 

I found the answer. Smiley Tongue

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


All Replies
Solution
‎03-22-2017 05:15 AM
Contributor
Posts: 21

Re: How to export a single table into multiple Excel sheets?

Okay everyone! 

I found the answer. Smiley Tongue

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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