topic Re: Importing tab delimited files in SAS Data Management

Importing tab delimited files

pronabesh — Tue, 25 Mar 2014 16:50:18 GMT

Hi SAS Community,

I am trying to import a tab delimited file (sample file attached) where the first line is the variable names and lines 2 and 3 contain data from one id (should align as a single row in the SAS file). I used the following Import syntax:

proc import out=temp datafile='C:\Users\template.txt' dbms=dlm replace;

delimiter='09'x;

guessingrows=32767;

getnames=yes;

run;

The imported file is not in proper format and the variable names don't align with the variable values. I suspect this has to do with special characters in the character variables but for the life of me, I cant figure out how to fix it.

Any help will be much appreciated!

Best,

Pronabesh

Re: Importing tab delimited files

ballardw — Tue, 25 Mar 2014 17:07:40 GMT

SAS cannot guess correctly that two rows of data are a single record, it will align first column, second etc. according to the delimiter provided. I don't see any special characters, which are you concerned with.

You may need to investigate the details of the input statement regarding reading multiple lines and such.

You didn't provide much in the way of data to do any testing and you will want to specify what the expected result is for example data.

Re: Importing tab delimited files

data_null__ — Tue, 25 Mar 2014 17:18:22 GMT

Did you create Template.txt with an editor? I see '0d0d0a'x follow by " double quote on the next line.

I'm thinking that '0d0a' was created when you saved it.

Can you show a bit of the original unaltered file.

Re: Importing tab delimited files

pronabesh — Tue, 25 Mar 2014 17:18:41 GMT

Thanks ballardw,

The SQL data miners provided me this data as tab delimited. I am assuming that every variable should be delimited by tab. However if I run my syntax on the sample dataset (agreed this is only a small sample) the delimiting does not work. I used '09'x as the delimiter.

Re: Importing tab delimited files

pronabesh — Tue, 25 Mar 2014 17:38:27 GMT

Hello Data_Null,

Thanks for your response. Here is a bigger cut of the data (see attached). I can send you the unadulterated file too but will need to strip some columns.

Re: Importing tab delimited files

data_null__ — Tue, 25 Mar 2014 18:27:37 GMT

Run this program on the original file. Find the place with the '0d'x and show the output. I want to see what follows the '0d'x in the original unaltered file.

filename FT55F001 'path-to-original-file.txt' lrecl=100 recfm=F;
data _null_; 
   infile FT55F001;
   input; 
   list; 
   if _n_ gt 20 then stop; 
   run;

This is what I see now but I think that gets "created" when you modify the file so you can post it.

6   CHAR  ted_at..2222.First Degree Burns...First Degree Burns."First degree burns are those in which only the
    ZONE  7665670033330467772466766247767000467772466766247767024677726667662677672676276676266276666266672766
    NUMR  454F14DA222296923404572550252E39996923404572550252E3926923404572550252E30125048F3509E0789380FEC90485
7          outer layer of skin is burned, but not all the way through. The skin is usually red and sometimes c

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      

8   CHAR  an swell or be painful. It is the least serious type of burn to have. ..."..t.f.10016700.First degre
    ZONE  6627766626726627666676224726727662666772767667727776266267762762667622000200706033333333046777266676
    NUMR  1E0375CC0F20250019E65CE09409304850C513403529F53049050F60252E04F08165E0DDA299496910016700969234045725

Re: Importing tab delimited files

pronabesh — Tue, 25 Mar 2014 21:23:35 GMT

The issue was carriage return and linefeed characters within quoted strings.

Here is the solution

/*----------------------------------*/

/* Import txt files to SAS files */

/*----------------------------------*/

/*------------------------------------------------------------------------*/

/* Remove carriage return and linefeed characters within quoted strings */

/*------------------------------------------------------------------------*/

data _null_;

infile 'C:\Users\Pronabesh\Desktop\SQLtoSAS\condition.txt' recfm=n sharebuffers;

file 'C:\Users\Pronabesh\Desktop\SQLtoSAS\condition.txt' recfm=n;

input a $char1.;

retain open 0;

/* This statement toggles the open flag. */

if a='"' then open=not open;

if a='0A'x and open then put ' ';

run;

/*----------------------------------*/

/* Import txt files to SAS files */

/*----------------------------------*/

proc import datafile='C:\Users\Pronabesh\Desktop\SQLtoSAS\condition.txt'

out=temp2

dbms=dlm replace;

delimiter='09'x;

guessingrows=32767; * Specifies the number of rows of the file to scan to determine the appropriate data type and length for the columns;

getnames=yes;

run;