DATA Step, Macro, Functions and more

Please help me to figure out what is wrong with my Excel file

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

Please help me to figure out what is wrong with my Excel file

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!


Accepted Solutions
Solution
‎02-04-2013 04:59 PM
Respected Advisor
Posts: 4,640

Re: Please help me to figure out what is wrong with my Excel file

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


All Replies
Super User
Posts: 17,730

Re: Please help me to figure out what is wrong with my Excel file

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.

Super Contributor
Posts: 1,636

Re: Please help me to figure out what is wrong with my Excel file

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

Respected Advisor
Posts: 4,640

Re: Please help me to figure out what is wrong with my Excel file

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
Super Contributor
Posts: 1,636

Re: Please help me to figure out what is wrong with my Excel file

Thank you PG!

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

Solution
‎02-04-2013 04:59 PM
Respected Advisor
Posts: 4,640

Re: Please help me to figure out what is wrong with my Excel file

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
Super Contributor
Posts: 1,636

Re: Please help me to figure out what is wrong with my Excel file

Hi PG and Reesa,

Thank you for your help!:smileylaugh:

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 396 views
  • 6 likes
  • 3 in conversation