topic Re: SAS Export data with double quotes escaped with backslash. in SAS Programming

SAS Export data with double quotes escaped with backslash.

ADN — Tue, 19 Mar 2024 05:03:28 GMT

I have a SAS dataset which I want to export as csv with each value enclosed around double quotes(").

Now one of the values is as Arijit "Debnath.

Currently the ds2csv macro exports it as "Arijit ""Debnath"

I want it to be exported as "Arijit \"Debnath".

Is there any way to achieve the same.

Re: SAS Export data with double quotes escaped with backslash.

Mazi — Tue, 19 Mar 2024 06:51:48 GMT

Hi,

Can you send a sample of the dataset and the macro test please?

Re: SAS Export data with double quotes escaped with backslash.

Patrick — Tue, 19 Mar 2024 10:13:39 GMT

If I save an Excel sheet as .csv with a cell value of Arijit "Debnath then I see in the .csv text file opened via a text editor a string "Arijit ""Debnath"

If I use Proc Export to create this .csv then the string in the created .csv text file becomes "Arijit ""Debnath"

If I create a .csv via a text editor and add string "Arijit \"Debnath" and open this .csv via Excel then the cell value shows as Arijit \Debnath"

I'm not sure what you're trying to achieve but it looks to me the valid csv representation of your source string should be "Arijit ""Debnath" which is what Proc Export creates.

Re: SAS Export data with double quotes escaped with backslash.

yabwon — Tue, 19 Mar 2024 11:31:12 GMT

The \" looks like escape for double quotes in JSON string.

Re: SAS Export data with double quotes escaped with backslash.

Patrick — Tue, 19 Mar 2024 11:37:02 GMT

@yabwon wrote:

The \" looks like escape for double quotes in JSON string.

The OP mentioned explicitly .csv which is why I ignored the possibility that this might be an escape character.

Re: SAS Export data with double quotes escaped with backslash.

yabwon — Tue, 19 Mar 2024 11:52:12 GMT

something like this may help:

data have;
  a=42;
  b='Arijit "Debnath';
  c=1000000;
  length d $ 50;
  d="ABCDEFGH";
run;

filename f "%sysfunc(pathname(work))/test.csv";

%ds2csv(csvfref=f,data=have,runmode=B)

data _null_;
  infile f SHAREBUFFERS;
  file f;
  input;
  _infile_ = tranwrd(_infile_,' ""',' \"');
  put _infile_;
run;

[EDIT:]

I assume there is a space before "" in the string, so the situation like:

"ABC""DEF"

or 

"""ABCD"

won't be solved.

[EDIT2:]

Small update to fix leading or trailing multiple quotes like: """AB" or "AB"""":

data have;
  a=42;
  b='Arijit "Debnath';
  c=1000000;
  length d $ 50;
  d="ABCD""EFGH";
  e="""ABCDEFGH";
  f="ABCDEFGH""";
run;

filename f "%sysfunc(pathname(work))/test.csv";

%ds2csv(csvfref=f,data=have,runmode=B)

data _null_;
  infile f SHAREBUFFERS;
  file f;
  input;
  _infile_ = tranwrd(_infile_,'"""','"\"');
  _infile_ = tranwrd(_infile_,'""','\"');
  put _infile_;
run;

Bart

Re: SAS Export data with double quotes escaped with backslash.

Tom — Tue, 19 Mar 2024 14:17:28 GMT

@ADN wrote:

I have a SAS dataset which I want to export as csv with each value enclosed around double quotes(").

Now one of the values is as Arijit "Debnath.

Currently the ds2csv macro exports it as "Arijit ""Debnath"

I want it to be exported as "Arijit \"Debnath".

Is there any way to achieve the same.

Sounds like your target system wants some strange (unix shell based perhaps) variant of CSV. I would first check if there is not an option in that system that will allow it to accept a NORMAL CSV file with normal handling of double quote characters. Most databases that accept that strange format also have options to accept normal CSV files instead.

You could also go find the SAS Ballot item I proposed many many years ago for SAS to enhance their support for delimited file generation (actually don't waste your time as they already said they didn't want to do it.)

If not then you might be able to post process the file and just replace any "" with \". So if the lines are not longer than 32K you could do something like:

data _null_;
  infile 'orginal.csv' ;
  file 'adjusted.csv';
  input;
  _infile_=tranwrd(_infile_,'""','\"');
  put _infile_;
run;

Here is a more complicated data step that should be able to transform any CSV into that non-standard format. Basically it reads each field value. It it starts with a double quote it removes the outer quotes and replaces the doubled up imbedded quotes with "escaped" quotes instead and then adds the outer quotes back.

You can add LRECL= options on the FILE and INFILE statement to support files with lines longer then 32767 bytes. You also need to select length for the VALUE variable that is longer than the maximum field values width. For this example I have used 200 bytes, but SAS can support up to 32767. (For best performance don't use too long a value, SAS string operations really slow down with long strings.)

data _null_;
  infile csv dsd truncover column=cc length=ll ;
  file notcsv ;
  do field=1 by 1 until (cc>ll);
    input value ~ :$200. @;
    if value=:'"' then value=cat('"',tranwrd(substr(value,2,length(value)-2),'""','\"'),'"');
    len=lengthn(value);
    if field>1 then put ',' @;
    put value $varying200. len @;
  end;
  put;
run;