topic Re: proc import got unwanted variable when converting a tab-delimited file in New SAS User

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

jkl123 — Sun, 30 Aug 2020 03:27:16 GMT

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.

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

Cecillia_Mao — Sun, 30 Aug 2020 03:59:17 GMT

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;

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

Kurt_Bremser — Sun, 30 Aug 2020 07:23:43 GMT

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.

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

prajakta — Sun, 30 Aug 2020 07:58:14 GMT

Try using below -

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

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

ballardw — Sun, 30 Aug 2020 10:49:54 GMT

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.

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

jkl123 — Sun, 30 Aug 2020 15:30:04 GMT

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

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

Kurt_Bremser — Sun, 30 Aug 2020 16:27:57 GMT

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.

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

jkl123 — Sun, 30 Aug 2020 23:32:51 GMT

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.