BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Linlin
Lapis Lazuli | Level 10

Hi All,

The attached excel file has one date column. The last three rows (with date=11/26/2012) produce missing value after reading into sas (I use libname). I can't figure out why. Any help is highly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

The problem is that Excel is only scanning 8 lines by default before determining the column type. You can change that behaviour, as recommended by SAS, by setting TypeGuessRows=0 in the Windows registry. Look at the MIXED= YES|NO paragraph in the documentation page :

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

PG

PG

View solution in original post

6 REPLIES 6
Reeza
Super User

SAS is reading them in as numbers, or trying to at least. But if you look at the formula bar in excel they have a apostrophe (') before the date (probably because excel was trying to divide the numbers).

I think you can get rid of it by formatting the cells as text and then removing the apostrophe's and re-importing.

Linlin
Lapis Lazuli | Level 10

Thank you Reeza! Is there a way to get rid of the '?

PGStats
Opal | Level 21

The apostrophe is an old Excel convention that forces a string that looks like a number to remain a text value. The only way I know to get all the values is to use the MIXED=YES option on the LIBNAME statement. You will get a character-type column for your dates that you will have to transform using myDate = input(date, mmddyy10.).

PG

PG
Linlin
Lapis Lazuli | Level 10

Thank you PG!

I still got missing values after adding " mixed=yes" to the libname statement.

PGStats
Opal | Level 21

The problem is that Excel is only scanning 8 lines by default before determining the column type. You can change that behaviour, as recommended by SAS, by setting TypeGuessRows=0 in the Windows registry. Look at the MIXED= YES|NO paragraph in the documentation page :

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

PG

PG
Linlin
Lapis Lazuli | Level 10

Hi PG and Reesa,

Thank you for your help!:smileylaugh:

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
  • 1244 views
  • 6 likes
  • 3 in conversation