BookmarkSubscribeRSS Feed
angeliquec
Quartz | Level 8

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?

6 REPLIES 6
Aman4SAS
Obsidian | Level 7

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.

angeliquec
Quartz | Level 8

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?

Aman4SAS
Obsidian | Level 7

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.

SASKiwi
PROC Star

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

angeliquec
Quartz | Level 8

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!

SASKiwi
PROC Star

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.

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
  • 6 replies
  • 1654 views
  • 0 likes
  • 3 in conversation