proc import got unwanted variable when converting a tab-delimited file

[jkl123]

Hello, everyone,

I try to use PROC IMPORT to convert a tab-delimited file "class.txt", located at 'C:\base-guide-practice-data\cert' , to a sas data set. Here is my code:

PROC IMPORT OUT= WORK.CLASS
DATAFILE= "C:\base-guide-practice-data\cert\class.txt"
DBMS=TAB REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

There are 3 variables in "class.txt": Name, Gender, Age , But I just got 1 variable: Name_____Gender___Age. All these three are joined together. The log shows the following:

data WORK.CLASS ;
35 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
36 infile 'C:\base-guide-practice-data\cert\class.txt' delimiter='09'x MISSOVER DSD
36 ! lrecl=32767 firstobs=2 ;
37 informat Name_____Gender___Age $20. ;
38 format Name_____Gender___Age $20. ;
39 input
40 Name_____Gender___Age $
41 ;
42 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
43 run;

Does someone also get the same issue? I use SAS university edition. Any suggestions? Thanks.

[Cecillia_Mao]

You can try to use the dlm option. The see the example below:

proc import datafile="C:\temp\test.txt"
out=test
dbms=dlm
replace;
dlm=',';
run;

[Kurt_Bremser]

Please open your text file with Notepad, and copy/paste the first few lines into a window opened with the </> button, so we can see exactly how it is structured.

[prajakta]

Try using below -

proc import datafile = "\path\file.txt"
out = test
dbms = dlm
replace;
dlm = '09'x;
run;

[ballardw]

That behavior means that your actual file is very likely NOT tab delimited.

If the columns are "nicely" aligned it may mean that this is actually fixed column and not delimited.

As mentioned, copy a few lines of the file and paste into a code box opened on the forum with the </> icon.

The code box is critical in this case as the message windows will reformat text and the result will almost certainly be other than your actual file.

BTW, did you ever open that "tab" delimited file in Excel? Excel is also known to change things if you aren't extremely careful.

[jkl123]

Hello, ballardw ,

Thank you for your info. My answers to your concern:

(1) This is the example of the base-performance exam book.

(2) I never opened tab-delimited file with Excel.

(3) I tried infile & input statements to read this file and succeeded last night. I believe it's column-fixed file. 

(4) The book said it's a tab-delimited file, so I never suspect that point. 

(5) See the original file:

Name     Gender   Age                                                           
Joyce    F        11                                                            
Thomas   M        11                                                            
Jane     F        12                                                            
Louise   F        12                                                            
James    M        12                                                            
John     M        12                                                            
Robert   M        12                                                            
Alice    F        13                                                            
Barbara  F        13                                                            
Jeffery  M        13                                                            
Carol    F        14                                                            
Judy     F        14                                                            
Alfred   M        14                                                            
Henry    M        14                                                            
Jenet    F        15                                                            
Mary     F        15                                                            
Ronald   M        15                                                            
William  M        15                                                            
Philip   M        16 

[Kurt_Bremser · Accepted solution]

There are no tabs in there, the data as posted is fixed-width. This data step reads it:

data want;
input @1 Name $8. @10 Gender $1. @12 Age 10.;
datalines;                                                        
Joyce    F        11                                                            
Thomas   M        11                                                            
Jane     F        12                                                            
Louise   F        12                                                            
James    M        12                                                            
John     M        12                                                            
Robert   M        12                                                            
Alice    F        13                                                            
Barbara  F        13                                                            
Jeffery  M        13                                                            
Carol    F        14                                                            
Judy     F        14                                                            
Alfred   M        14                                                            
Henry    M        14                                                            
Jenet    F        15                                                            
Mary     F        15                                                            
Ronald   M        15                                                            
William  M        15                                                            
Philip   M        16
; 

Remove the DATALINES block and add a simple INFILE statement (no delimiter needed) to point to your file.

[jkl123]

Hi KurtBremser , thank you. Yes, you're right. I saved that original file
as tab-delimited and tried the code again. Everything worked well. The
issue is my external file is not tab-separated. Thanks for everyone's help.