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

[Kaos]

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!

[ballardw]

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.

[Kaos]

@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!

[Patrick]

@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;

[s_lassen]

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;

[Kaos]

@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!

[Ksharp · Accepted solution]

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;