DATA Step, Macro, Functions and more

import of txt (csv) file truncates variable length

Accepted Solution Solved
Reply
Contributor das
Contributor
Posts: 44
Accepted Solution

import of txt (csv) file truncates variable length

xtGreetings,

I'm am having trouble importing a csv (txt) file saved from another program. Here is my code:

PROC IMPORT OUT= WORK.BERNSEN_IMPORT

     DATAFILE= "E:\SAMB-IHC\F89_titration\G8-G9\measure\Bernsen.txt"

     DBMS=TAB REPLACE

     ;

     GETNAMES=YES;

     DATAROW=2;

RUN;

Here is the problem area in the file to be imported as opened in notepad; note that the second variable increases in length from 13 to 14 as the value of "r" goes from 9 to 11:

Capture.PNG

Here is the incorrectly imported datafile; not the loss of the number following "t" in the second variable:

Capture2.PNG

And, although the log doesn't report any problems that I can identify, there does seem to be the automatic definition of the second variable (which happens to be "Label") as length of 13. Here is a screen capture:

Capture3.PNG

Is there a way to improve proper import parameter setting?

Thank you,

Dave


Accepted Solutions
Solution
‎03-06-2014 11:48 AM
Super User
Posts: 17,737

Re: import of txt (csv) file truncates variable length

Take the code (as displayed above) and modify that and use it instead of proc import.

Proc Import guesses, but it gives you the code, so you can specify.

EDIT. You can select only the code, not line numbers from the log by holding down ALT and dragging the mouse to highlight the code.

View solution in original post


All Replies
N/A
Posts: 1

Re: import of txt (csv) file truncates variable length

PROC IMPORT OUT= WORK.BERNSEN_IMPORT

     DATAFILE= "E:\SAMB-IHC\F89_titration\G8-G9\measure\Bernsen.txt"

     DBMS=TAB REPLACE

     ;

     GETNAMES=YES;

     DATAROW=2;

guessingrows=159503;

RUN;

Try~~~~

Contributor das
Contributor
Posts: 44

Re: import of txt (csv) file truncates variable length

Thank you, zhaowei331. That worked but caused the import to take about 2 minutes as opposed to 3 seconds to complete.

Dave

Super User
Posts: 10,460

Re: import of txt (csv) file truncates variable length

My experience with this reinforces Reeza suggestion to save the datastep code and look REAL close at the informats applied.

Your example variable with a problem would suggest to me that you may well want to make it several characters longer even than the successfull import. Also, are some of the fields that imported as numeric actually desired as numbers? If they are really codes or stock numbers that at some file in the future might contain characters and you need to combine datasets you'll have to go through extra steps to process the data.

Valued Guide
Posts: 2,174

Re: import of txt (csv) file truncates variable length

das

As points out, proc import might not be your best solution.

I posted an answer earlier on this. You might find it useful to guide you through the best alternative approach. See https://communities.sas.com/thread/52897/#196856

gool luck

peterC

Occasional Learner
Posts: 1

Re: import of txt (csv) file truncates variable length

Peter_c,

 

The link in the post is directing to the home page but not to the solution.

 

Can you please post the direct link to the solution you were talking about in the post.

 

Thanks,

Neelakant

Solution
‎03-06-2014 11:48 AM
Super User
Posts: 17,737

Re: import of txt (csv) file truncates variable length

Take the code (as displayed above) and modify that and use it instead of proc import.

Proc Import guesses, but it gives you the code, so you can specify.

EDIT. You can select only the code, not line numbers from the log by holding down ALT and dragging the mouse to highlight the code.

Contributor das
Contributor
Posts: 44

Re: import of txt (csv) file truncates variable length

Thank you Reeza. It took me a few tries to get the coding just right (like extracting out the macro statements) but now I see that this is a useful way troubleshoot and fix such a lengthy import. This executed much faster with correct length variable 2 (Label). I like it!

Many thanks to the other responders. the guessingrows option for proc import was new to me and works also, but, with the length of read necessary in this datafile, it took a few minutes to process the import. I will read on to learn more from the related comments and link provided by ballardw and Peter.C

I am so impressed with response times and help I can get from this support community. That is a real plus.

Thank you all,

Dave

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 11684 views
  • 3 likes
  • 6 in conversation