The SAS Output Delivery System and reporting techniques

How to preserve the SAS formats in .xlsb file?

Reply
Super Contributor
Posts: 416

How to preserve the SAS formats in .xlsb file?

Code below is working fine. However, I'm not getting the right values in the file when I used the same code to generate .xlsb file.

Working:

ODS tagsets.excelxp file="/data/temp/&fi._autorecapture_reports_customers_&tday..xls" style=statistical ;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='AutoRecaptureResponses');

PROC REPORT DATA=response NOWD;

COLUMN  loan_amt savamt  saving_range  Auto_Balance balance_range;

DEFINE loan_amt / format=dollar15.2;

DEFINE savamt / format=Dollar8.0;

DEFINE Auto_Balance / format=Dollar8.0;

run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Recapture_mailed');

Proc report data=freqout ;

column maildate mindate maxdate fico_range  saving_range balance_range count ;

run;

ODS tagsets.ExcelXP CLOSE;

Not Working:

ODS tagsets.excelxp file="/data/temp/&fi._autorecapture_reports_customers_&tday..xlsb" style=statistical ;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='AutoRecaptureResponses');

PROC REPORT DATA=response NOWD;

COLUMN  loan_amt savamt  saving_range  Auto_Balance balance_range;

DEFINE loan_amt / format=dollar15.2;

DEFINE savamt / format=Dollar8.0;

DEFINE Auto_Balance / format=Dollar8.0;

run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Recapture_mailed');

Proc report data=freqout ;

column maildate mindate maxdate fico_range  saving_range balance_range count ;

run;

ODS tagsets.ExcelXP CLOSE;

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: How to preserve the SAS formats in .xlsb file?

Well, not entirely sure why that is.  At a guess I would say the structure of an XLSB is slightly different.  The extension .xls just indiactes to your OS that the file should be opened in Excel, hence when it is opened you get the message stating its not in the expected format - xls is Excel format file, you are actually generating XML.  The XLS is an older binary format, so (again, guesswork), I imagine that Excel in that instance looks at the file and sees are yes XML, use XML parser.  With the XLSB file, which is a macro enabled Excel file its slightly different so maybe confusing Excel into not reading certain parts of the file.

Two points though,

Firstly, you may want to use: define XYZ / "xyz" style(column)=[tagattr='format:#,##']; For applying formats to the data.

SEcondly, why are you wanting to create an XLSB in the first place?  SAS Creates and text XML file which Excel interprets.  There isn't a reason to want to create a macro enabled XLS which has no actual macros?

Grand Advisor
Posts: 17,308

Re: How to preserve the SAS formats in .xlsb file?

XLSB is a binary file.

The file created from tagsets is an XML file.

Even if you added the extension XLSB and it did create the file you wouldn't get a true XLSB file so this is a pointless exercise.

If you need a native XLSB file you need to first create the XML file and then re-save it as XLSB. This could be accomplished via DDE or a VB macro - sample code for both exist on this forum.

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: How to preserve the SAS formats in .xlsb file?

Actually, you are not entirely correct there.  XLSB is much like XLSX as in its an open office document.  However there are binary components contained within the ZIP file, for instance VBA macros are compiled and stored in a proprietary binary file within the XLSB folder structure, worksheets are stored as binary.

It does beg the question why the OP wants to create XLSB files, could it be he thinks to create smaller file sizes like the old XLS format did?

Esteemed Advisor
Posts: 6,636

Re: How to preserve the SAS formats in .xlsb file?

ODS TAGSETS.EXCELXP always creates XML data, so the file should be named with a .xml extension to avoid confusion. When doing a compare of your output files, you will find they are identical (apart from the <Created> and <LastSaved> tags). You therefore will get the same effect by creating a .xls file in SAS and then externally renaming it to .xlsb.

That Excel treats the perfectly same data differently because of a different filename extension is NOT a SAS problem, but an Excel one, and I am not surprised you run into another shortcoming of the clusterfuck that Excel has become.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 821 views
  • 0 likes
  • 4 in conversation