topic Re: Import issue in SAS Programming

Import issue

hhchenfx — Tue, 10 Oct 2017 03:52:50 GMT

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

Re: Import issue

Patrick — Tue, 10 Oct 2017 04:16:38 GMT

@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

Re: Import issue

Reeza — Tue, 10 Oct 2017 04:42:35 GMT

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.

Re: Import issue

andreas_lds — Tue, 10 Oct 2017 04:48:40 GMT

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.

Re: Import issue

Kurt_Bremser — Tue, 10 Oct 2017 06:48:32 GMT

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.

Re: Import issue

hhchenfx — Tue, 10 Oct 2017 15:52:16 GMT

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.

Re: Import issue

Kurt_Bremser — Tue, 10 Oct 2017 16:45:44 GMT

@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.

Re: Import issue

KrisDeng — Fri, 01 Dec 2017 20:58:31 GMT

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!

Re: Import issue

Kurt_Bremser — Mon, 04 Dec 2017 07:20:20 GMT

@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.