topic Re: How to import .txt files in SAS Software for Learning Community

How to import .txt files

nbav — Wed, 08 Dec 2021 07:02:23 GMT

I am trying to import .txt files (I have attached the files below). Here is the code I wrote:

proc import datafile='/home/u58841625/Cert/class.txt' dbms=tab out=class
replace;
delimiter='09'x;
run;

proc import datafile="/home/u58841625/Cert/state_data.txt" out=work.state
dbms=tab replace;
delimiter='09'x;
run;

The output tables are expected to have 3 and 4 columns respectively but there is only a single column in both tables. (All the columns are merged into one column. Images attached below). Can anyone please explain to me what is the mistake in my code?

Thanks in advance.

Re: How to import .txt files

Kurt_Bremser — Wed, 08 Dec 2021 07:47:15 GMT

Open both text files with a good text editor (e.g. Notepad++) which can display special characters and/or has a hex display mode, before uploading, to verify which delimiter is used.

Re: How to import .txt files

Ksharp — Wed, 08 Dec 2021 11:42:35 GMT

Your TXT is delimited by white blank not TAB character.

proc import datafile='/home/u58841625/Cert/class.txt' dbms=DLM out=class
replace;
delimiter=' ';
run;

proc import datafile="/home/u58841625/Cert/state_data.txt" out=work.state
dbms=CSV replace;
delimiter=' ';
run;

Re: How to import .txt files

nbav — Sun, 12 Dec 2021 16:34:06 GMT

Thank you for your reoly. I have opened both files in notepad++ and there were no special characters.

Re: How to import .txt files

nbav — Sun, 12 Dec 2021 16:37:57 GMT

Thank you for your reply. I have tried this code before. It is partially working. Please look at the images below I am getting so many unwanted columns with missing values. Is there a solution to get rid of those unwanted columns??

Re: How to import .txt files

Tom — Sun, 12 Dec 2021 19:50:43 GMT

There is no need to "import" a text file. Just READ the text file.

It looks like your files do NOT have any tabs in them.

The CLASS file seems to be using multiple spaces so the columns will align when printed/viewed with a fixed width font.

Name     Gender   Age                                                           
Joyce    F        11                                                            
Thomas   M        11                                                            
Jane     F        12                                                            
Louise   F        12

So just read them "normally" and see if that works.

data class ;
  infile '/home/u58841625/Cert/class.txt' firstobs=2 truncover ;
  input name :$20. gender :$1. age ;
run;

The other file

Region State Capital Bird                                                       
South Georgia Atlanta 'Brown Thrasher'                                          
South 'North Carolina' Raleigh Cardinal                                         
North Connecticut Hartford Robin                                                
West Washington Olympia 'American Goldfinch'                                    
Midwest Illinois Springfield Cardinal

appears to have a single space between the values and quotes around values that contain spaces. So use the DSD option with space as the delimiter.

data state;
  infile '/home/u58841625/Cert/state_data.txt' dsd dlm=' ' firstobs=2 truncover;
  input Region :$10. State :$30.  Capital :$30.  Bird :$40. ;
run;

Re: How to import .txt files

nbav — Mon, 13 Dec 2021 08:45:45 GMT

Thank you. Your solution worked. But I am still trying to find out how to import those files.

Re: How to import .txt files

Kurt_Bremser — Mon, 13 Dec 2021 08:58:17 GMT

@Tom gave you working code for both files. Where is the issue?

Re: How to import .txt files

nbav — Mon, 13 Dec 2021 09:17:54 GMT

I am a student and learning SAS by SAS Certified Specialist preparation guide. I am practicing all the examples given in the guide and only these two import procedures failed. (Reference: SAS Certified Specialist preparation guide, pg: 41, Importing a Tab-Delimited File).

Example code which they gave is:

proc import datafile='C:\Users\Student1\cert\class.txt'
dbms=tab
out=class
replace;
delimiter='09'x;
run;

I am not able to understand why the code is not working.

Re: How to import .txt files

Kurt_Bremser — Mon, 13 Dec 2021 10:26:26 GMT

Whenever code gives you issues, please post the complete log of a failing step by copy/pasting it directly from your SAS interface (SAS Studio, Enterprise Guide) into a window opened with this button:

As already mentioned, your files as posted do not use tabs, but white space. Maybe the original tabs were replaced by blanks during the whole transport operation.

Next, I suspect that you do not have SAS installed on your desktop or on any Windows system, instead using SAS On Demand. ODA runs on a UNIX platform, you have to upload the files first and then use the UNIX pathname to address them. Retrieve the correct name by right-clicking on the file after the upload and copying it from the Properties.

Re: How to import .txt files

Ksharp — Mon, 13 Dec 2021 12:43:53 GMT

Try Tom's code a.k.a import txt file via data step!

Re: How to import .txt files

nbav — Mon, 13 Dec 2021 16:51:12 GMT

Thank you. Yes, I am using the SAS On-Demand (SAS studio not installed on my computer). I am using windows not UNIX.

I have checked my log there are no errors but some notes are as follows:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc import datafile='/home/u58841625/Cert/class.txt' dbms=tab out=work.class

74 replace;

75 delimiter='09'x;

76 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

77 /**********************************************************************

78 * PRODUCT: SAS

79 * VERSION: 9.4

80 * CREATOR: External File Interface

81 * DATE: 13DEC21

82 * DESC: Generated SAS Datastep Code

83 * TEMPLATE SOURCE: (None Specified.)

84 ***********************************************************************/

85 data WORK.CLASS ;

86 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

87 infile '/home/u58841625/Cert/class.txt' delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2 ;

88 informat "Name Gender Age"N $20. ;

89 format "Name Gender Age"N $20. ;

90 input

91 "Name Gender Age"N $

92 ;

93 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

94 run;

NOTE: The infile '/home/u58841625/Cert/class.txt' is:

Filename=/home/u58841625/Cert/class.txt,

Owner Name=u58841625,Group Name=oda,

Access Permission=-rw-r--r--,

Last Modified=14Dec2021:05:22:59,

File Size (bytes)=1620

NOTE: 19 records were read from the infile '/home/u58841625/Cert/class.txt'.

The minimum record length was 80.

The maximum record length was 80.

NOTE: The data set WORK.CLASS has 19 observations and 1 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 9373.93k

OS Memory 41508.00k

Timestamp 12/13/2021 04:36:16 PM

Step Count 168 Switch Count 2

Page Faults 0

Page Reclaims 173

Page Swaps 0

Voluntary Context Switches 13

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 272

19 rows created in WORK.CLASS from /home/u58841625/Cert/class.txt.

NOTE: WORK.CLASS data set was successfully created.

NOTE: The data set WORK.CLASS has 19 observations and 1 variables.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.07 seconds

user cpu time 0.03 seconds

system cpu time 0.02 seconds

memory 9373.93k

OS Memory 41768.00k

Timestamp 12/13/2021 04:36:16 PM

Step Count 168 Switch Count 10

Page Faults 0

Page Reclaims 2614

Page Swaps 0

Voluntary Context Switches 89

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 328

96 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

108

Re: How to import .txt files

Kurt_Bremser — Mon, 13 Dec 2021 22:24:02 GMT

You ARE using UNIX, SAS On Demand runs solely on Linux, which is a UNIX variant.

And you still try to use tabs as delimiters; we have told you several times by now that your files do not contain tabs.

Run @Tom's code.