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;
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?
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.
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?
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.