Import within Enterprise Guide giving incorrect data types

Reply
Super Contributor
Posts: 400

Import within Enterprise Guide giving incorrect data types

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

Super User
Posts: 11,343

Re: Import within Enterprise Guide giving incorrect data types

Posted in reply to jerry898969

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.

 

 

Super Contributor
Posts: 400

Re: Import within Enterprise Guide giving incorrect data types

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

Super User
Posts: 11,343

Re: Import within Enterprise Guide giving incorrect data types

Posted in reply to jerry898969

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?

Ask a Question
Discussion stats
  • 3 replies
  • 340 views
  • 1 like
  • 2 in conversation