Import within Enterprise Guide giving incorrect data types

[jerry898969]

Hello,

I have a text file is is comma delimited.  The file has double quotes(") as the text identifier.

I have 2 similier columns.  One has data and the other one does not.

1,,

When EG imports this it says the first is numeric and the second column is character.  I thought it needed double quotes for it to be considered text and make that column character.  I want that column to be numeric like the first one.   I do have columns where there are double quotes and those are character.

I could manually change it in EG, but I have thousends of columns and I want to make sure they are getting the correct data type assigned to them.

Thank you

[ballardw]

The quotes are really only needed when the text value contains a comma.Different CSV generators use variations of rules but basically SAS is looking for the comma.

If you are using proc import then you may need to increase the guessingrows parameter to tell SAS to look at more rows before assigning a variable type. The default is 20 rows, so if a variable is missing for the first 20 rows it will get a character assignment. The maximum allowed value of guessingrows is 32767.

[jerry898969]

Thank you ballardw.

I'm using  guessingrows=2147483647.

This is the issue that is causing me the trouble

Say I have the following data to import

id,empid,officeid,bldg,keyid,elevtor

"fsslk9493",2,,"West","",

What I need is for the variables to have the following data types

id  = character(9)

empid = numeric 8

officeid = numeric 8

bldg = character(4)

keyid = character(1)

elevtor = numeric 8

What is currently happening is the following:

id  = character(9)

empid = numeric 8

officeid = character(1)

bldg = character(4)

keyid = character(1)

elevtor = character(1)

Thank you

[ballardw]

General comment on using proc import: It can help but is seldom the end solution unless your data is extremely clean.

If you know what the variables are and should be, use the DATA STEP code generated by the proc import, copy into the editor and make the relatively minor changes to informat and re-run that code.

You may have abosolutely NO values in those columns that you want as numeric but came as 1 character fields. What do you get when you run proc freq on officeid and elevtor?