topic Re: Importing delimited file with text qualifiers in SAS Programming

Importing delimited file with text qualifiers

Konakanchi — Tue, 27 Mar 2018 11:14:11 GMT

Hi,

I am trying to read in a pipe delimited file through SAS. There are consecutive apostrophes in data. So my data looks like

A|B|'|'|||X|G

While reading in, I used DSD in INFILE. However, the delimted enclosed in ' is considered as data and placed in the SAS data set. This pis prompting data shift and the readin is messed. Is there a way to explicitly define text qualifier in SAS? Or any alternate solution?

Thank you!

Re: Importing delimited file with text qualifiers

Ksharp — Tue, 27 Mar 2018 12:20:55 GMT

How about get rid of these single quote firstly ?

data have;
infile cards truncover dsd dlm='|';
input @;
_infile_=compress(_infile_,"'");
input (v1-v8) (: $40.);
cards;
A|B|'|'|||X|G
;
run;

Re: Importing delimited file with text qualifiers

ballardw — Tue, 27 Mar 2018 14:25:48 GMT

@Konakanchi wrote:

Hi,

I am trying to read in a pipe delimited file through SAS. There are consecutive apostrophes in data. So my data looks like

A|B|'|'|||X|G

While reading in, I used DSD in INFILE. However, the delimted enclosed in ' is considered as data and placed in the SAS data set. This pis prompting data shift and the readin is messed. Is there a way to explicitly define text qualifier in SAS? Or any alternate solution?

Thank you!

I would be very tempted to contact whoever made that file to find out what a field with a single quote is supposed to actually mean. A single row is hard to diagnose data file issues but perhaps the quotes are there because the originator intended the third variable to actually contain a pipe symbol. How many pipe symbols appear on other rows of data?

BTW you do not have consecutive apostrophes in data. That would look like A|B|''|||X|G and would make some sense.

Re: Importing delimited file with text qualifiers

Tom — Wed, 28 Mar 2018 04:20:49 GMT

Normally I would suggest that you ask the person sending you the file to fix it, but it looks like you have found a bug in how SAS generates delimited files.

filename test temp;
data _null_;
  length x1-x8 $8 ;
  file test dsd dlm='|';
  x1='A';  x2='B';  x3="'";  x4="'";
  x5=' ';  x6=' ';  x7='X';  x8='G';
  put (x1-x8) (+0) ;
  putlog (x1-x8) (=);
run;

data _null_;
  infile test dsd dlm='|' truncover;
  input (x1-x8) ($) ;
  put (x1-x8) (=);
  list;
run;

x1=A x2=B x3=| x4=  x5=  x6=X x7=G x8=
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         A|B|'|'|||X|G 13
NOTE: 1 record was read from the infile TEST.

You can scan that line if you use the 'm' modifier without the 'q' modifier.

  array y (8) $ ;
  do i=1 to dim(y) ;
    y(i) = scan(_infile_,i,'|','m');
  end;

If you are generating the file using SAS you could add the ~ modifier to force SAS to write quotes around all of the values.

data _null_;
  length x1-x8 $8 ;
  file test dsd dlm='|';
  x1='A';  x2='B';  x3="'";  x4="'";
  x5=' ';  x6=' ';  x7='X';  x8='G';
  put (x1-x8) (~) ;
run;

"A"|"B"|"'"|"'"|" "|" "|"X"|"G"

Re: Importing delimited file with text qualifiers

Konakanchi — Wed, 28 Mar 2018 09:12:28 GMT

Tried scanning without m identifier earlier. Data shift was observed. When m identifier is used, the issue is resolved. Data is populating correctly without any readin issue by populating blanks in fields wherever it is ' alone. Thank You.