DATA Step, Macro, Functions and more

How to retain SAS formats when the dataset is converted to Excel?

Reply
Contributor
Posts: 54

How to retain SAS formats when the dataset is converted to Excel?

How is this possible?

We are applying this code options missing = "-"; . It works for displaying missing values as "-" in the SAS dataset but when we use proc export to create the excel (.XLS) file, the format is not applied. The SAS format is applied when we use proc export to create CSV file. Then we just do the manual conversion of CSV to XLS in Microsoft Excel.


We have also tried using ODS TAGSETS.EXCELXP file = "<filename>.xls" but when we open the file in Microsoft Excel, we get a warning:


The file format and extension of 'FILENAME.XLS' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?

Super Contributor
Posts: 265

Re: How to retain SAS formats when the dataset is converted to Excel?

for this warning need not to worry. Basically tagset create XML file which u need to open it  on excel and after that u need to save as excel workbook.

at the same time u find ur missing format will be there NOTE: "-" this missing option is not advisable.

Contributor
Posts: 54

Re: How to retain SAS formats when the dataset is converted to Excel?

Hi Aman4SAS! Thank you for the response!

Thus in the XML to XLS conversion, there would still be manual intervention. We would like to speed up this process since there are a lot of files to be output (more than 20 files).

May I know why the missing option is not advisable? I found that this also affects any logic like "where value = "." ". "-" was needed instead, so I had to create another session for the missing option. Is there an alternative to the missing option?

Super Contributor
Posts: 265

Re: How to retain SAS formats when the dataset is converted to Excel?

using "-" for missing value is not advisable. every tool has its own limitation and solution vary desired output to output. there are 3 way

1- create dashboard template for files and create refrence with ur output excel files. in this case u just need to refresh ur templates.

2- use tagset that will provide desired formatted output, but in this ur need to save as excel

3-compromise with ur format and export excel . and once set ur format to excel. from next time excel export file will create file as format set by you. but in this u will get only one row for ur header.

Super User
Posts: 3,106

Re: How to retain SAS formats when the dataset is converted to Excel?

You can avoid the warning with this: ODS TAGSETS.EXCELXP file = "<filename>.xml"

Contributor
Posts: 54

Re: How to retain SAS formats when the dataset is converted to Excel?

Hi SASKiwi! I have tried your suggestion but the output looks like an html code. Should there be an additional step to convert the XML to XLS? Thank you!

Super User
Posts: 3,106

Re: How to retain SAS formats when the dataset is converted to Excel?

The Excelxp tagset produces an XML version of an Excel workbook. If you want to convert it to XLS you can do a File Save As in Excel.

Ask a Question
Discussion stats
  • 6 replies
  • 650 views
  • 0 likes
  • 3 in conversation