09-12-2016 04:13 AM
Hello, i have a problem with importing csv file.
the file contains from two columns, one numeric(id), and one character(name).
This Csv file was created as export from sql.
When i use proc import procedure i get that two my variables are character i use options getnames=yes and datarow=2).
But what is interesting, that if i open this file in excel and save it as Csv file, and then use import proc, i get one variable numeric and one character.
What is problem? What i can do? Can i use proc import?
09-12-2016 05:15 AM
Set GuessingRows to the max number of observations.
Proc import guesses at types. If you have two variables write a datastep to import your data. You have full control over the types.
For an example see the log after your proc imports. You can see the differences in your two iterations.
09-12-2016 06:51 AM
Unfortunately it doen't help. I don't want to write Infile statement
Well, you are at the point of an important decision.
Do you want to become a competent SAS user, or remain a button-pusher?
The best solution for importing a csv is a manually written data step that sets all attributes to your needs. And immediately fails when the import structure has accidentally changed from what you expected.
(I'm not snarky here, I write from the perspective of ~20 years experience with SAS and data warehousing)
09-12-2016 09:14 AM
Answering your specific questions:
1. What is problem?
Proc Import guesses at types. There must be some difference in the CSV that causes SAS to see them differently. Excel can mess with values. If it looks like a date it will automatically convert it or removing leading zeroes. Your better off using the file from SQL
2. What can you do?
Change GuessingRows to increase number of rows scanned before determining a variable type.
Control variable type explicitly using data step
3.Can I use Proc Import?
Sure, but you run the risk of inconsistent data. If only reading one dataset this isn't an issue. If you're reading many datasets it can get annoying fast.
Writing a data step to explicitly define types is the only way to have full control. It's the same in most languages, if you want to control types you need to define them somehow. Computers only know what we tell them.
What specifically about infile don't you want to deal with?