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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Ksharp
Super User

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;
ballardw
Super User

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

Tom
Super User Tom
Super User

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"

 

 

 

Konakanchi
Calcite | Level 5

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.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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