DATA Step, Macro, Functions and more

Exporting multiple data sets to different sheets in one Excel book

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Exporting multiple data sets to different sheets in one Excel book

Good Afternoon!

 

I am trying to write an export proc to export mutiple data sets into different sheets in one Excel book.  Please see my query below:

 

proc export data=results.TownChanges outfile='\\athena\Projects\Kenya EABL RA\Data Management\SAS Setup\Kenya EABL MRA Field Queries.xlsx' dbms=xlsx replace;
sheet=TownChanges;
run;

 

I continuesly get the same error:  ERROR: DBMS type XLSX not valid for export.

 

I could succesfully export to seperate CSVs but no matter what other format I use, I get an error.  Could someone please assist me?

 

Kind Regards

 


Accepted Solutions
Solution
‎10-05-2016 08:34 AM
Super User
Posts: 19,877

Re: Exporting multiple data sets to different sheets in one Excel book

Posted in reply to GonetteSmith

If you don't have a licence see this macro

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

As well, look into TAGSETS.EXCELXP

View solution in original post


All Replies
Super User
Posts: 19,877

Re: Exporting multiple data sets to different sheets in one Excel book

Posted in reply to GonetteSmith

What version of SAS to you have? Do you have SAS ACCESS to PC FILES license? 

 

Run the followijg to check your license. 

 

Proc setinit;run;

proc product_status; run;

Solution
‎10-05-2016 08:34 AM
Super User
Posts: 19,877

Re: Exporting multiple data sets to different sheets in one Excel book

Posted in reply to GonetteSmith

If you don't have a licence see this macro

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

As well, look into TAGSETS.EXCELXP

New Contributor
Posts: 4

Re: Exporting multiple data sets to different sheets in one Excel book

Thank you for the fast feedback. I do not seems to have a lisence to PC files. Unfortunately, I am a brand new SAS and neither the Macro or TAGSETS.EXCELXP makes any sense to me :-(

 

Can you please explain a bit more?

Super User
Posts: 19,877

Re: Exporting multiple data sets to different sheets in one Excel book

Posted in reply to GonetteSmith

The macro has a paper and presentation please go through them. 

 

Examples of Tagsets are abundant as well, here's a link to,the official demo. 

 

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

 

New Contributor
Posts: 4

Re: Exporting multiple data sets to different sheets in one Excel book

Thank for suggesting Tagsets.  It solved my problem.  I wrotes the below code and I now achieve the desired results.

 

ods tagsets.excelxp file="\\athena\Projects\Kenya EABL RA\Data Management\SAS Setup\Kenya EABL MRA Field Queries.xls" style=Statistical
options(sheet_name="Channel Changes"
ABSOLUTE_COLUMN_WIDTH='10'
autofit_height ='on'
wraptext='no'
embedded_titles='yes');
Title j=l "CONFIRM WHICH IS THE CORRECT CHANNEL FOR EACH OUTLET IN A SEPERATE COLUMN: TRADECHANNEL OR CHANNEL_PREVIOUS";

proc REPORT data=results.TownChanges style(header)={just=l};
run;

ods tagsets.excelxp options(sheet_name="Town Changes") style=Statistical;
Title j=l "CONFIRM WHICH IS THE CORRECT TOWN FOR EACH OUTLET IN A SEPERATE COLUMN: TOWN OR TOWN_PREVIOUS";

proc REPORT data=results.TownChanges style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Duplicates") style=Statistical;
Title j=l "REFER TO THE DUPLICATES BELOW AND PLEASE SPECIFY IN A SEPERATE COLUMN HOW THESE SHOULD BE TREATED: MERGE OR DELETE";

proc REPORT data=results.Duplicates style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Facings>Stock-Cold") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE COLD FACINGS ARE GREATER THAN FRIDGE STOCK. THERE CAN NEVER BE MORE FACINGS THAN STOCK PRESENT. PLEASE INDICATE THE CORRECT VALUE FOR TOTALSTOCK_FRIDGE AND/OR FACINGS_COLD IN A SEPERATE COLUMNS.
";

proc REPORT data=results.FACINGSGREATERTHANSTOCK_COLD style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Facings>Stock-Warm") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE WARM FACINGS ARE GREATER THAN WARM STOCK. THERE CAN NEVER BE MORE FACINGS THAN STOCK PRESENT. PLEASE INDICATE THE CORRECT VALUE FOR TOTALSTOCK_WARM AND/OR FACINGS_WARM IN A SEPERATE COLUMNS.
";

proc REPORT data=results.FACINGSGREATERTHANSTOCK_WARM style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Kegs with Case Information") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE KEGS/BARRELS HAVE CASE INFORMATION RECORDED. KEGS/BARRELS ARE NOT AVAILABLE IN CASES. PLEASE INDICATE THE CORRECT INFORMATION FOR THE BELOW INSTANCES IN A SEPERATE COLUMN.
";

proc REPORT data=results.KEGSWITHCASESIZES style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Case Size-Fridge") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE CASE INFORMATION WAS RECORDED FOR FRIDGE BUT THE CASE SIZE WAS NOT INDICATED. CASES INFORMATION NEEDS TO BE MULTIPLIED OUT TO SINGLE UNITS AND WE THEREFORE REQUIRE THE CASE SIZE FOR EACH CASE FIGURE RECORDED. PLEASE SPECIFY THE CASE SIZE FOR THE BELOW INSTANCES IN A SEPERATE COLUMN.
";

proc REPORT data=results.MISSINGCASESIZE_FRIDGE style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Case Size-Purchases") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE CASE INFORMATION WAS RECORDED FOR PURCHASES BUT THE CASE SIZE WAS NOT INDICATED. CASES INFORMATION NEEDS TO BE MULTIPLIED OUT TO SINGLE UNITS AND WE THEREFORE REQUIRE THE CASE SIZE FOR EACH CASE FIGURE RECORDED. PLEASE SPECIFY THE CASE SIZE FOR THE BELOW INSTANCES IN A SEPERATE COLUMN.
";

proc REPORT data=results.MISSINGCASESIZE_PURCHASES style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Case Size-Storeroom") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE CASE INFORMATION WAS RECORDED FOR STOREROOM BUT THE CASE SIZE WAS NOT INDICATED. CASES INFORMATION NEEDS TO BE MULTIPLIED OUT TO SINGLE UNITS AND WE THEREFORE REQUIRE THE CASE SIZE FOR EACH CASE FIGURE RECORDED. PLEASE SPECIFY THE CASE SIZE FOR THE BELOW INSTANCES IN A SEPERATE COLUMN.
";

proc REPORT data=results.MISSINGCASESIZE_STOREROOM style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Case Size-Warm") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE CASE INFORMATION WAS RECORDED FOR WARM BUT THE CASE SIZE WAS NOT INDICATED. CASES INFORMATION NEEDS TO BE MULTIPLIED OUT TO SINGLE UNITS AND WE THEREFORE REQUIRE THE CASE SIZE FOR EACH CASE FIGURE RECORDED. PLEASE SPECIFY THE CASE SIZE FOR THE BELOW INSTANCES IN A SEPERATE COLUMN.
";

proc REPORT data=results.MISSINGCASESIZE_WARM style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Diaries") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE OUTLETS HAVE BEEN REVISITED BUT NO PURCHASES WERE INDICATED. PLEASE SPECIFY THE MISSING PURCHASE INFORMATION IN A SEPERATE COLUMN OR OTHERWISE A REASON FOR THE MISSING PURCHASE INFORMATION.";

proc REPORT data=results.MISSINGDIARIES style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Keg Glass Sizes") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES SERVING PRICES INDICATED FOR KEGS/BARREL BUT NOT GLASS SIZE SPECIFIED. SERVINGS PRICES NEED TO BE MULTIPLIED OUT TO A FULL KEG PRICE AND WE THEREFORE NEED THE GLASS SIZE. PLEASE SPECIFY THE MISSING GLASS SIZE OR OTHERWISE SUPPLY A PRICE FOR THE FULL KEG/BARREL IN SEPERATE COLUMNS.";

proc REPORT data=results.MISSINGGLASSSIZES_KEGS style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Prices") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE THERE ARE STOCK AND/OR SALES INFORMATION BUT NO PRICE RECORDED. EVERY STOCK OR SALES INSTANCE REQUIRES A PRICE. PLEASE SPECIFY THE CORRECT PRICE AND SKU MEASURE ('SINGLE TOT', 'DOUBLE TOT', '250ML GLASS' OR 'FULL UNIT') FOR EACH OF THE BELOW INSTANCES IN SEPERATE COLUMNS";

proc REPORT data=results.MISSINGPRICES style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Missing Stock") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE THERE WAS NO STOCK RECORDED FOR THE WHOLE OUTLET. PLEASE SPECIFY THE MISSING STOCK INFORMATION IN A SEPERATE COLUMN OR OTHERWISE A REASON FOR THE MISSING STOCK INFORMATION";

proc REPORT data=results.MISSINGSTOCK style(header)={just=l} style(column)={height=0.5cm};
run;


ods tagsets.excelxp options(sheet_name="Negative Sales") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE SALES CALCULATE TO NEGATIVES WHICH IS NOT POSSIBLE. THIS MEANS THAT THE PURCHASES AND/OR STOCK INFORMATION ARE INCORRECT. PLEASE SPECIFY THE CORRECT PURCHASES AND/OR STOCK INFORMATIONS IN SEPERATE COLUMNS.";

proc REPORT data=results.NEGATIVESALES style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="NonNafuu With Senator Keg") style=Statistical;
Title j=l "THE BELOW ARE INSTANCES WHERE THE OUTLET STOCKS SENATOR KEG BUT ARE CLASIFIED AS A NON NAFUU OUTLET. PLEASE CONFIRM THE CHANNEL FOR EACH OF THE OUTLETS.";

proc REPORT data=results.NONNAFUUWITHSENATORKEG style(header)={just=l} style(column)={height=0.5cm};
run;

ods tagsets.excelxp options(sheet_name="Outlets Not In Quota") style=Statistical;
Title j=l "THE BELOW OUTLETS WERE VISITED OUTSIDE OF THE AREAS REQUIRED IN THE QUOTA. THESE OUTLETS WILL BE EXCLUDED. PLEASE DO NOT REVISIT THESE OUTLETS NEXT READ.";

proc REPORT data=results.OUTLETSNOTINQUOTA style(header)={just=l} style(column)={height=0.5cm};
run;

 

ods tagsets.excelxp options(sheet_name="Auditspan Outside Range") style=Statistical;
Title j=l "THE BELOW OUTLETS ARE INSTANCES WHERE THE ALLOWABLE AUDIT SPAN HAS NOT BEEN ADHERED TO. THESE OUTLETS WILL NOT BE ACCEPTED. STICK TO THE ALLOWABLE AUDIT SPAN OF 28-32 DAYS";

proc REPORT data=results.auditspanoutsiderange style(header)={just=l} style(column)={height=0.5cm};
run;

ODS tagsets.ExcelXP CLOSE;

Super User
Super User
Posts: 7,997

Re: Exporting multiple data sets to different sheets in one Excel book

Posted in reply to GonetteSmith

I would avoid proc export, you have greater control with ods tagsets.excelxp:

ods tagsets.excelxp file="abc.xlsx" options=(sheet_name="MyFirstSheet");
proc report data=xyz;
 ...;
run;
ods tagsets.excelxp options=(sheet_name="MySecondSheet");
proc report data=ert;
 ...;
run;
ods tagsets.excelxp close;

You can push out all kinds of funky formats, and styling and such like through the report, plus apply filters etc.

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Valued Guide
Posts: 505

Re: Exporting multiple data sets to different sheets in one Excel book

I have not tried this but I heard that the libname engine is now part of workstation sas(sometimes called pc sas).

 

This should copy all data sets in library sashelp to multiple sheets in a workbook

 

libname xel "d:/xls/sashelp.xlsx";

proc copy in=sashelp out=xel;

run;quit;

 

I nice feature of this is that you get nice sheetnames and a named ranges with the same name as the sheet.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 375 views
  • 0 likes
  • 4 in conversation