10-13-2014 02:14 AM
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?
10-13-2014 02:33 AM
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.
10-13-2014 02:40 AM
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?
10-13-2014 02:51 AM
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.
10-13-2014 10:18 PM
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!
10-14-2014 12:19 AM
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.