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

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?

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

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?

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.