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?
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.
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?
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.
You can avoid the warning with this: ODS TAGSETS.EXCELXP file = "<filename>.xml"
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.