DATA Step, Macro, Functions and more

Importing delimited file with text qualifiers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Importing delimited file with text qualifiers

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!


Accepted Solutions
Solution
‎03-28-2018 05:12 AM
Super User
Super User
Posts: 8,279

Re: Importing delimited file with text qualifiers

Posted in reply to Konakanchi

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"

 

 

 

View solution in original post


All Replies
Super User
Posts: 10,849

Re: Importing delimited file with text qualifiers

Posted in reply to Konakanchi

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;
Super User
Posts: 13,941

Re: Importing delimited file with text qualifiers

Posted in reply to Konakanchi

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

Solution
‎03-28-2018 05:12 AM
Super User
Super User
Posts: 8,279

Re: Importing delimited file with text qualifiers

Posted in reply to Konakanchi

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"

 

 

 

Occasional Contributor
Posts: 11

Re: Importing delimited file with text qualifiers

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.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 164 views
  • 0 likes
  • 4 in conversation