BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Patrick
Opal | Level 21

@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

Reeza
Super User

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. 

 

andreas_lds
Jade | Level 19

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.

KrisDeng
Obsidian | Level 7

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!

Kurt_Bremser
Super User

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

Kurt_Bremser
Super User

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.

hhchenfx
Barite | Level 11

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

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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