DATA Step, Macro, Functions and more

Error using PROC IMPORT

Reply
Contributor n6
Contributor
Posts: 62

Error using PROC IMPORT

Someone sent me four text files.  I'm using PROC IMPORT to bring them in.  Three of them worked fine but the other gives me an error.

 

I think it will be messy to copy and paste the whole file so maybe someone can help me figure it out just from the Log window message.  Here is what it says.

 

Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

 

No data in the first 5 records?  It looks to me like there's data from the very beginning.  Any assistance is appreciated.

 

BTW, I've always found it odd that it says in the SAS Log "See SAS Log for details"  Also, SAS has been around so long that you'd think that they could put possible solutions in the log when an error happens.  I mean, this will probably get solved by someone here telling me "Maybe the problem is X" and then I check X.  Since there are people that know what the potential problems are for a particular error message it seems that SAS could just add to the error message in the Log window "Potential problems are X, Y and Z."  Then peope could try those on their own and if still not successful then they could come here or go to SAS Support.  Just a thought.

Super User
Super User
Posts: 7,955

Re: Error using PROC IMPORT

Its hard to say really, there could be special characters in the file which your not seeing straight off, try looking at the file in a hex editor (HexEdit for instance).  Special characters will show up there.  You may also have an odd delimiter or something like that.  Have you tried using the datastep created by proc import and using that - I would always use a datastep and infile to read text data, this error for instance is telling you that the step import does does first is to read the file plain as a sample to try to guess what your data looks like.  You can skip that totally by telling SAS what the data looks like, so saving resource and importing the file as you know the data.  It probably wont solve your issue here as there is something funny going on.  

Super User
Posts: 7,786

Re: Error using PROC IMPORT

hexedit, hexdump or any text editor that can display a file in hex code. You have to get a clear picture what's really in the file. The answer lies there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Error using PROC IMPORT

Are the files supposed to be of the same structure? Then only use Proc import once, copy the data step code generated by the procedure into the editor, verify that the variable informats and types match expectations and reread all 4 files using the same code changing the input file name on the infile statement and the output data set name.

 

You may get more detailed error messages OR possibly no error depending on the actual issue, though blank records may be possible.

Contributor n6
Contributor
Posts: 62

Re: Error using PROC IMPORT

Hmm, I didn't even realize Hexidecimal Editors were a thing.  I'm at work and when I add new software to my computer I have to get our computer guy to enter credentials.  I can do that if necessary but I'll try to figure it out otherwise first.

 

So is what I'm hearing is that there can be characters in the text file that I just can't see when I open it and they can be messing things up when I do the import?  If that's the case maybe there's no choice but HexEdit.

 

As far as copying the datatep from the ones that did work I'm not sure I could do that correctly since I'd have to change it to fit the one that didn't work.  Maybe I'll give it a try.

 

Thanks for all the response.  I'll keep hacking away at it.

Super User
Posts: 11,343

Re: Error using PROC IMPORT


n6 wrote:

 

 

As far as copying the datatep from the ones that did work I'm not sure I could do that correctly since I'd have to change it to fit the one that didn't work.  Maybe I'll give it a try.

 

Thanks for all the response.  I'll keep hacking away at it.


If you know what the variable names, types and formats should be writing a data step to read text files is a fairly basic skill. If you don't have anything to tell what you should have in those terms then how do you know that your proc import results is correct? Proc Import has to guess variable characteristics from the content of the file and depending on the file type and options set when using import may not yield desired results.

A search here with "Import" will show literally hundreds of posts related to the guessing results using proc import not getting the desired results or things like variables changing types from one import result to the next for what is supposed to be the same variable.

Contributor n6
Contributor
Posts: 62

Re: Error using PROC IMPORT

When I do a data step myself I don't have embedded blanks in variables.  So if I have a variable value that is "San Francisco" for instance, I"ll use "San_Francisco" in my data step.  I know you can get around that by using @ signs to indicate the exact spaces in which the data resides but I rarely do that.  But anyway, the data I'm importing does have embedded blanks.  I assume that PROC IMPORT knows how to magically deal with that (or maybe it doesn't and that's my problem).  But if I used a data step then I'd have to deal with it.  For example, the value of some of the variables are like ">150 but <200."  Without special instruction SAS will think that is three values instead of just one.

 

Also, this text file has the variable names and then the data in a long string with it wrapping now and then for reasons I know not.  There are about 15 variables so first it gives the 15 variable names then it gives the 15 variable values for the 1st observation, then for the 2nd obseravation, etc.  And there are maybe 20 observations in a row and then it wraps to the next row but it wraps mid-observation or sometimes even mid-variable.  So if the value of the variable is "Yes" sometimes it has "Ye" at the end of a row and then "s" at the beginning of the next row.  So with all that craziness I don't think I could write a data step for it and  have it work

Contributor n6
Contributor
Posts: 62

Re: Error using PROC IMPORT

If I went to SAS Support do you think I could attach the file so they could see exactly what is going wrong?  I'd have to ask the researcher that sent me the data for permission of course but otherwise maybe that's the easiest way to deal with this.

PROC Star
Posts: 7,474

Re: Error using PROC IMPORT

@n6: You don't need a special text editor to get a hex dump of your file. The following macro was posted on the SAS-L forum, by Mark Terjeson, in 2005. It produces a hex dump in your log:

 

%macro hexdump(pathfile);
data _null_;
    length chars $16;
    retain address 0
           chars '................';
    infile "&pathfile" lrecl=1 recfm=f end=done;
    input c $char1.;
    if mod(address,16) eq 0 then
        do; 
            if _N_ eq 1 then put address hex8. 'h: ' @;
            else put ' ; ' chars / address hex8. 'h: ' @;
            chars = '................';
        end;
    put c hex2. ' ' @;
    if rank(c) ge 32 then 
        substr(chars,mod(address,16)+1,1) = c;
    address + 1;
    if done then
        do; 
            do i = mod(address,16)+1 to 16;
                put ' ' @;
            end;
            put ' ; ' chars / ;
        end;
run;
%mend;

%hexdump(c:\temp\have.txt);

Art, CEO, AnalystFinder.com

Contributor n6
Contributor
Posts: 62

Re: Error using PROC IMPORT

Thanks for that, it certainly produced something.  I'm not sure what to do with it but it produced a lot of stuff.  I can see that on the right it's the data in my text file seemingly with each bit separated by a period.  To the left it has a bunch of numbers or number/letter combos.  Any hints on how I can interpret this?

Super User
Posts: 11,343

Re: Error using PROC IMPORT

You might post a the first few lines in a code box using the forum {i}

or look up what a hexidecimal number and ASCII values are.

 

Contributor n6
Contributor
Posts: 62

Re: Error using PROC IMPORT

I don't know what forum {i} is but here is the first five lines of what it gave me.  Any ideas?  If you recall, the original error message was that the first five lines had no records, or something like that.  They look like they have records to me.

 

00000000h: 53 61 6D 70 6C 65 49 44 09 73 75 62 6A 65 63 74 ; SampleID.subject
00000010h: 09 77 74 5F 63 6C 61 73 73 09 64 61 79 33 36 5F ; .wt_class.day36_
00000020h: 72 65 73 70 6F 6E 73 65 33 09 64 61 79 33 36 5F ; response3.day36_
00000030h: 72 65 73 70 6F 6E 73 65 34 09 64 61 79 36 36 5F ; response4.day66_
00000040h: 72 65 73 70 6F 6E 73 65 33 09 64 61 79 36 36 5F ; response3.day66_

Super User
Posts: 19,815

Re: Error using PROC IMPORT

The {i} refers to the 6th icon in the editor. You NEED to post it using that otherwise it's posted as HTML and we can't see if there are invisible characters or such. 

 

Another common issue for this is a different TERMSTR espeically if you're working between different systems, ie Mac/Windows/Unix at the same time. 

 

See the options here and test them with your FILENAME. 

http://support.sas.com/kb/14/178.html

Super User
Super User
Posts: 7,050

Re: Error using PROC IMPORT

In the past we have seen that error with PROC IMPORT when the file was created using CR as end of line character. That is somehting that Excel on Mac will do if you are not careful in how you save data your spreadsheet to a CSV file.  If that is the problem then the simple fix is use a FILENAME statement and add the TERMSTR=CR.

filename myfile 'myfile.csv' termstr=cr ;
proc import datafile=myfile .....

You can use a data step to view the contents of a file.

data _null_;
  infile 'myfile.csv' obs=10;
  input;
  list;
run;

 

PROC Star
Posts: 7,474

Re: Error using PROC IMPORT

You have to post more lines than you did. You likely only posted part of the files first line.

 

What was clear, from what you did post, is that the file is TAB delimited.

 

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 15 replies
  • 287 views
  • 1 like
  • 7 in conversation