BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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;

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3745 views
  • 1 like
  • 4 in conversation