SAS: Exporting Data Subsets to Separate Workbook Tabs in Excel

Question

Question: How can I export subsets of a data set to individual tabs of an Excel workbook (preferably .xlsx) without running PROC EXPORT several times?

My Solution

The data set contains 15 indicators. Unfortunately, the indicators do not have names which can be indexed. This means I cannot put the export procedure into a macro which loops over a counter 15 times and appends the name with the index. The indicators are (not really) things like "car", "truck", "bicycle", "dinosaur", etc.

The solution I came up with was like this:

proc export data = data_set 
                (where = (indicator = "car"))
            outfile = "c:\workbook.xlsx"
            dbms = xlsx replace;
            sheet = car;
run;

...

proc export data = data_set 
                (where = (indicator = "dinosaur"))
            outfile = "c:\workbook.xlsx"
            dbms = xlsx replace;
            sheet = dinosaur;
run;

However, this is obviously inefficient and begs for some sort of automation.

What version of SAS do you have? If 9.4 ODS Excel can be used automatically, otherwise use Tagsets but it will create an xml not xlsx file. — Reeza
– Reeza, Commented Aug 16, 2016 at 22:56
I am using SAS 9.4. I am not sure what other products or maintenance updates I have. How would I check on that? And thank you for asking, this is information I should remember to provide when asking questions. :) — Lorem Ipsum
– Lorem Ipsum, Commented Aug 17, 2016 at 1:11

Joe · Accepted Answer · 2016-08-16 21:45:03Z

1

You can use the libname facility, which is what proc export uses in the background usually.

libname myexcel xlsx "c:\outwhatever\myfile.xlsx";  *can use XLSX if 9.4+ or EXCEL if earlier;

That gives you a regular libname just as if it were a SAS libname, and you can write to it like so:

data myexcel.sheetname;
  set whatever;
run;

Or use PROC COPY or similar.

There are other options (using OLEDB or similar, for example), but libname is simplest. See the documentation for more details.

answered Aug 16, 2016 at 21:45

Joe

63.5k7 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Reeza · Accepted Answer · 2016-08-17 00:56:06Z

1

If you have SAS 9.4 ODS Excel is quite simple and nice, set the sheet_interval option to bygroup and add a prefix for the sheet name.

proc sort data=sashelp.class out=class;
by age;
run;

ods excel file='/folders/myfolders/sample.xlsx' options (sheet_interval='bygroup' sheet_label='Age');

proc print data=class noobs label;
by age;
run;

ods excel close;

answered Aug 17, 2016 at 0:56

Reeza

21.4k4 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

SAS: Exporting Data Subsets to Separate Workbook Tabs in Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related