BookmarkSubscribeRSS Feed
Zaneta
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,
Zaneta
1 REPLY 1
Kurt_Bremser
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.

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!

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.

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
  • 1 reply
  • 2327 views
  • 0 likes
  • 2 in conversation