DATA Step, Macro, Functions and more

Import issue

Accepted Solution Solved
Reply
Super Contributor
Posts: 503
Accepted Solution

Import issue

Hi Everyone,

 

I import a csv file into SAS using the code below. in 1 cell, the csv value is 2000 (no comma) but in SAS, it become 2 (and become character). No error notice show up.

proc import datafile="...csv"
out=Payroll dbms=csv replace;
getnames=yes;
run;

So I try the 

data Payroll ;
infile "&data_path.\Payroll Register Data Export - &month..csv"
dlm=','  dsd truncover firstobs=2;

and the value turns out correct 2000 (best12)

 

(the first few row in the data is blank but many other colum return correct value)

 

I wonder why it is like that and what method of import abve is more reliable?

 

I try to save as this few row into a seperate file in order to put it here so you can try but this new file works fine with both approaches.

 

Thank you for your help.

 

HHC

 


Accepted Solutions
Solution
‎10-10-2017 11:59 AM
Super User
Posts: 9,574

Re: Import issue

Concur with @Patrick. Use an advanced editor like notepad++ to view the file (you can switch to hexadecimal display), so you can see what's really in there.

 

Or read the file with a brute-force method:

data test;
infile "&data_path.\Payroll Register Data Export - &month..csv";
length my_line $200 my_hex_line $400;
input;
my_line = _infile_;
my_hex_line = put(my_line,$hex400.);
run;

and then look at the hex display of the input. If necessary, expand the lengths.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Respected Advisor
Posts: 4,545

Re: Import issue

@hhchenfx

Looks to me like you might have some hidden characters or the like in your source file - especially because you can't even copy/paste the rows into another file to replicate the issue.

 

What you could do:

-  Open the file with Notepad ++ or similar and search for non-print characters (View / Show Symbol / Show all characters)

or...

- Save your file under a different name, delete most of your rows/just leave a few including the one causing issues

- Run your code on this new file and if the issue still exists then attach the file to your post so we can investigate

Super User
Posts: 22,850

Re: Import issue

After your IMPORT step, check your log. See the FORMAT / INFORMAT applied for the variable in question. 

Then compare it to your data step code and see the differences. 

 

Super Contributor
Posts: 498

Re: Import issue

[ Edited ]

Have you tried setting GUESSINGROWS to 100 in proc import?

And: if you know in which line data starts, use DATAROW to tell it proc import.

Contributor
Posts: 22

Re: Import issue

Posted in reply to andreas_lds

Actually this helps a ton. I had this problem and when I saw your coment, I tried to put Guessingrows={value} and it worked wonder. I'd like to know what's the difference when I put the value 10000 vs 100 or any others.

 

Thank you very much!

Super User
Posts: 9,574

Re: Import issue


KrisDeng wrote:

Actually this helps a ton. I had this problem and when I saw your coment, I tried to put Guessingrows={value} and it worked wonder. I'd like to know what's the difference when I put the value 10000 vs 100 or any others.

 

Thank you very much!


It simply determines how may lines of the input file proc import will read to guess the data structure. So more lines may create a better result, but will also take longer, as proc import scans the file twice (once for guess, twice for actual read). But this only helps in guessing, for consistent  results a properly written data step is the only way to go.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎10-10-2017 11:59 AM
Super User
Posts: 9,574

Re: Import issue

Concur with @Patrick. Use an advanced editor like notepad++ to view the file (you can switch to hexadecimal display), so you can see what's really in there.

 

Or read the file with a brute-force method:

data test;
infile "&data_path.\Payroll Register Data Export - &month..csv";
length my_line $200 my_hex_line $400;
input;
my_line = _infile_;
my_hex_line = put(my_line,$hex400.);
run;

and then look at the hex display of the input. If necessary, expand the lengths.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 503

Re: Import issue

Posted in reply to KurtBremser

Thanks for helping me.

Since this column has all cell blank above this error cell, What I do is to put number 1 in the first cell and SAS now CORRECTLY import the data.

Does this give any clue as to what should I do?

 

I am not sure what I should do since I have to use IMPORT as the column in the data file is not fix from file to file.

 

HC

Super User
Posts: 9,574

Re: Import issue


hhchenfx wrote:

.... as the column in the data file is not fix from file to file.

 


This is what needs fixing. Once a certain action has to be repeated consistently, a solid agreement on data structures is mandatory.

Proc import relies on guesses and cannot produce consistent results.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 296 views
  • 2 likes
  • 6 in conversation