BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pronabesh
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
pronabesh
Fluorite | Level 6

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;

View solution in original post

6 REPLIES 6
ballardw
Super User

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.

pronabesh
Fluorite | Level 6

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.

data_null__
Jade | Level 19

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.

pronabesh
Fluorite | Level 6

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.

data_null__
Jade | Level 19

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
pronabesh
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 4904 views
  • 3 likes
  • 3 in conversation