topic Re: Creating Delimited File with Embedded delimiter in Data values in SAS Programming

Creating Delimited File with Embedded delimiter in Data values

PRAVIN_JAIN — Thu, 13 May 2021 07:47:25 GMT

I need to convert dataset into flat file with "}" as delimiter. One of the field in dataset has } in data values due to which it is being considered as an extra column.

Dataset -

Var1

Accepted

Accepted}

{Accepted}

How to handle this while converting into flat file, flat file will be read by Python program.

Re: Creating Delimited File with Embedded delimiter in Data values

japelin — Thu, 13 May 2021 08:40:35 GMT

I think there is a problem with python reading, but you could try using $QUOTE. format to handle quoting.

data have;
  length var1 var2 $20;
  input var1 var2;
datalines;
Accepted Accepted
Accepted} Accepted}
{Accepted} {Accepted}
;
run;

filename x temp ;
data _null_;
  set have;
  file x dlm='}';
  format var1-var2 $QUOTE.;
  put var1 var2;
run;

Result is below.

Re: Creating Delimited File with Embedded delimiter in Data values

ChrisNZ — Thu, 13 May 2021 09:22:17 GMT

It depends on what software is reading the flat file. Another option (besides quoting as suggested) is to escape the } by adding a \ before }, using for example function transtrn().

Re: Creating Delimited File with Embedded delimiter in Data values

PRAVIN_JAIN — Thu, 13 May 2021 11:56:28 GMT

This means we enclose data values in " " and handle these quoted values in Python ?.

Re: Creating Delimited File with Embedded delimiter in Data values

Kurt_Bremser — Thu, 13 May 2021 12:19:37 GMT

Any software that reads/writes delimited files has mechanisms to deal with embedded delimiters, usually by using quotes. In SAS this is done with the DSD option:

data _null_;
file "outfile" dlm="}" dsd;
set have;
put var1;
run;

This will automatically enclose values containing the delimiter in double quotes.

Re: Creating Delimited File with Embedded delimiter in Data values

Tom — Thu, 13 May 2021 13:52:45 GMT

SAS will normally generate readable delimited files. When the value contains the delimiter the whole value is enclosed in quotes. Because of this any file that contains a quote is also enclosed in quotes.

data have;
  input row (var1-var2) (:$20.);
datalines;
1 Accepted   Yes
2 Accepted}  No
3 {Accepted} "Maybe"
;

filename csv temp;
data _null_;
  set have;
  file csv dsd dlm='}';
  put (_all_) (+0);
run;

Results:

1}Accepted}Yes
2}"Accepted}"}No
3}"{Accepted}"}"""Maybe"""

Check the python program to make sure it is reading the file properly.

If you are desperate you might preprocess the data to add \ in front of and delimiters. A lot of programs that take their clues for how to process text for the conventions of unix shell scripts use that character to "escape" the next character. But in that case make sure to also escape and escape characters in the strings.

data have;
  input row (var1-var2) (:$20.);
datalines;
1 Accepted   Yes
2 Accepted}  No
3 {Accepted} Yes\No
;
filename csv temp;
data _null_;
  set have;
  array _c _character_;
  do over _c;
    _c=tranwrd(_c,'\','\\');
    _c=tranwrd(_c,'}','\}');
  end;
  file csv  dlm='}';
  put (_all_) (+0);
run;

Results

1}Accepted}Yes
2}Accepted\}}No
3}{Accepted\}}Yes\\No