DATA Step, Macro, Functions and more

How to Read a single quote as data value from a tab-delimited text file?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

How to Read a single quote as data value from a tab-delimited text file?

Hello!

 

I am trying to read from a tab-delimited text file and running into issues with single quote as a data value. Below is the data set I am working on-

 

Col1   Col2   Col3   Col4   Col5

Apple            '          '         5

Pear   4        '          '         

Grapes  6        2        3       7   

 

The Code I used -

 

Data test_file;

    Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;

    Input Col1 :$Char20.

             Col2 :$Char20.

             Col3 :$Char20.

             Col4 :$Char20.

             Col5 :$Char20.;

Run;

 

Problem: My code is reading the tab between the consecutive single quotes as the data value, instead of the single quotes as individual data value (for col3 and col4).

 

I am able to read single quote as a data value when I do not use "DSD" option. However, with missing values in my raw data, I need to use DSD. 

 

Can anyone suggest a solution to resolve this issue?

 

Any help will be much appreciated!

 

Thanks!

       


Accepted Solutions
Solution
‎05-24-2017 12:59 PM
Super User
Posts: 9,681

Re: How to Read a single quote as data value from a tab-delimited text file?

Why not translate it into other character and translate it back ?

 

Data test_file;

    Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;

 

input @;

_infile_=translate(_infile_,'.',"'");

 

    Input Col1 :$Char20.

             Col2 :$Char20.

             Col3 :$Char20.

             Col4 :$Char20.

             Col5 :$Char20.;

Run;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: How to Read a single quote as data value from a tab-delimited text file?

How many files like this are you going to have to read?

If it is only a few then a kludge is to replace the single quote with another uncommon character such as ~ with a text editor before reading and then replace after reading.

 

But I am not quite understanding why you are using char20 or did you leave that from your real program but didn't realize that may not work for your example data? Also have you tried using an INFORMAT statement instead of including the informat on the input statement?

Such as:

Data test_file;
    Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;
    informat Col1 - Col5  $Char20. ;
    Input Col1 
          Col2 
          Col3 
          Col4 
          Col5 ;
Run;

 

 

You may need to attach a text file with a few lines of example data as the forum reformats stuff, especially when pasted into the main windows and we cannot test code against what you post very well.

Occasional Contributor
Posts: 8

Re: How to Read a single quote as data value from a tab-delimited text file?

@ballardw

 

I have to read one file every day that may have single quote in some of the columns. This file is usually 2GB in size with about ~20 MM records so can't really open in text editor. However, I used _infile_ to replace the single quote with a different special character and that worked. 

 

Thanks for your input!

 

Respected Advisor
Posts: 3,890

Re: How to Read a single quote as data value from a tab-delimited text file?

@Kaos

That's a tricky one. Best idea I could come up with is to add a single blank between consecutive delimiters so you don't need option DSD.

data test;
  Infile "C:\temp\test.txt"  DLM='09'x MISSOVER;

  /* modify input buffer: add blanks between consecutive delimiters */
  input @;
  _infile_=prxchange("s/(\t)(?=\t)/\1 /",-1,_infile_);

  input 
    Col1 :$Char20.
    Col2 :$Char20.
    Col3 :$Char20.
    Col4 :$Char20.
    ;
run;

Capture.PNG

PROC Star
Posts: 63

Re: How to Read a single quote as data value from a tab-delimited text file?

[ Edited ]

Basically, you will want to replace the single quotes with something else before reading - but it is quite easy to have SAS do that, by manipulating the automatic variable _INFILE_ - I used "¤" here:

Data test_file;
    Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;
    _infile_=transwrd(_infile_,'09'x!!"'"!!'09'x,'09'x!!'¤'!!'09'x);
    if substr(_infile_,1,2)="'"!!'09'x then
      substr(_infile_,1,1)='¤'; /* first cell is single quote */
    if substrn(_infile_,length(_infile_)-1)='09'x!!"'" then
      substr(_infile_,length(_infile_))='¤'; /* last cell is a single quote */
    Input Col1 :$Char20.
             Col2 :$Char20.
             Col3 :$Char20.
             Col4 :$Char20.
             Col5 :$Char20.;
    array cols col1-col5;
    do _N_=1 to dim(cols);
      if cols(_N_)='¤' then
        cols(_N_)="'";
      end;
Run;

If you do not have any text that is quoted using single quotes, you can simplify the initial translation:

Data test_file;
    Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;
    _infile_=translate(_infile_,'¤',"'");
    Input Col1 :$Char20.
             Col2 :$Char20.
             Col3 :$Char20.
             Col4 :$Char20.
             Col5 :$Char20.;
    array cols col1-col5;
    do _N_=1 to dim(cols);
      cols(_N_)=translate(cols(_N_),"'",'¤');
      end;
Run;

 

 

Occasional Contributor
Posts: 8

Re: How to Read a single quote as data value from a tab-delimited text file?

@s_lassen

 

Thank you for your response. I used your second solution, and that worked with a minor change i.e. I had to include "Input @;" before translating single quote to a different special character.

 

Thanks again!

Solution
‎05-24-2017 12:59 PM
Super User
Posts: 9,681

Re: How to Read a single quote as data value from a tab-delimited text file?

Why not translate it into other character and translate it back ?

 

Data test_file;

    Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;

 

input @;

_infile_=translate(_infile_,'.',"'");

 

    Input Col1 :$Char20.

             Col2 :$Char20.

             Col3 :$Char20.

             Col4 :$Char20.

             Col5 :$Char20.;

Run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 146 views
  • 4 likes
  • 5 in conversation