BookmarkSubscribeRSS Feed
Calcite | Level 5
Hi everyone,

I am trying to export some data to Excel from SAS EG 7.1 using proc export and dbms = xls. It works well except that one of my numeric variables includes both ordinary missings and .S and both are converted to blanks in the export. Is there a way to retain the .S as this or some other value that distinguishes it from the ordinary missings?

One solution is to use dbms = .csv but I then have problems with losing leading zeros in a character variable. And I also can't export multiple SAS datasets to different worksheets in the same Excel file then.

And another is to convert the numeric variable to a character - this changes .S to S which is fine, but converts the ordinary numeric missings to . in Excel plus all numbers to text which isn't so helpful.

Is there anything else I could try?

Many thanks,
Super User

In Excel, a missing numerical value is always represented as an empty cell. So if you want to have the column as numeric data, blanks is what you get.

A workaround could be to assign special non-missing values for your .S (before you do the export), which you could then use with a user-defined format in Excel, if that is possible.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation