SAS: importing text file

Question

What is wrong with the imported code? I'm trying to import a text file into SAS.

proc import DATAFILE="C:\Users\Tiffany\Documents\Inpatientaveragelengthofstaysince2009.txt" 
   out=Indivs dbms=dlm replace;
   delimiter=' ';
   getnames=yes;
run;

I get this message below:

ERROR: Import unsuccessful. See SAS Log for details.

Answer 1

It's not clear from your question how you want the text file to be read in. Here is code to read each line of the file in as a separate observation.

data lines;
  infile 'C:\YourFile\here.txt' dlm = '```';
  length line $ 32767;
  input line;
run;

This works by setting the delimiter to a sequence of characters which you will not see in your file. I used three back tick marks in a row.

Answer 2

Further to the above comments, the line of the log above this line:

ERROR: Import unsuccessful. See SAS Log for details.

generally contains details of what the problem was.

An additional option to add to the import is "GUESSINGROWS=MAX;" which tells SAS to scan the entire file before deciding what each column actually is (in terms of size and type). As @MrfksIV put in their response, it's generally safest to use a datastep though as then you'll know exactly what is being read in, that's what I do 90% of the time.

Answer 3

Generally after lots of unsuccessful tries of proc import i decided that it is better to be avoided as it sometimes comes up with surprising results.

You can instead use the infile command inside of a datastep:

data input_data; 
infile "../../file.txt"
firstobs = 2
dlm = " "
missover
dsd
lrecl = 32767
input
   var1 : $char128. /*string field of 128 chars long*/
   var2 : best32.   /*numbers of all types ints, floats, etc */
   ...
   /*do this for every variable (column) in you .txt file */  
;
run;

The code above is more or less what the proc import does. The problem with proc import is that it tries to 'guess' the correct options (such as dsd, missover, etc.) and datatypes and it does not always succeed. You can find more info on what these options do here.

For example the missover option tells sas what to do in case it finds two consecutive delimiters. (Should this be treated as an empty column and leave one value blank as it reads the next one or just pull the next available value in?)

Of course, the code above is not very practical in cases of long txt files with a great number of columns as you would have to type each column label with its data type on your own.

One possible solution to this is:

data headers;
infile "../../file.txt"
firstobs = 1
obs = 1
dlm = "_" /*make sure to here to use a delimiter DIFFERENT from the real one to keep everything in one observation*/
missover
lrecl = 32767
input
   whole_line : $char32767.
;
run;

The above step creates a dataset that has one observation that contains all the column headers separated by your delimiter. Now you can use :

proc sql noprint;
select distinct tranwrd(compress(line),","," ") as lineTest length=32767     into: headers from headers length;
quit;

The above codes creates a macro-variable called headers with the column names separated by spaces (the tranwrd() function basically replaces the ',' with ' ' because I usually work with csv files so this might not be necessary for you. In general, using a space as a delimiter might not be a good idea - this applies if you have string fields containing spaces themselves. The error you get might actually be related to this)

Now you can loop through the macro variable instead of writing each column name and type one by one:

data inputFile (compress=binary) ;
    infile "../../file.txt"
    dsd
    dlm = ","
    lrecl = 32767
    firstobs = 2
    ;
    input
    %do i=1 %to %words(&headers);
        %let currCol = %scan(&headers. , &i.);
        &currCol : $char256.
    %end;
    ;
run;

This will create the dataset that you want without worrying if the file structure has changed (columns were added or removed) which makes your code more dynamic. Of course this leaves the problem that all values are imported as characters.

This is definitely an issue, however I have found that it is much easier type converting the variables on demand, rather than typing each variable one by one in the input statement.

If you will use the macro variable and the %do-loop, be sure to include all of your code in a macro, otherwise the loop will give an error.

I hope this helps!