topic Re: SAS export giving double quote itself in SAS Programming

SAS export giving double quote itself

Srigyan — Wed, 19 Dec 2018 14:17:08 GMT

I am trying to export SAS data into CSV, sas dataset name is abc here and format is

LINE_NUMBER	DESCRIPTION
524JG	24PC AMEFA VINTAGE CUTLERY SET "DUBARRY"

I am using following code.

filename exprt "C:/abc.csv" encoding="utf-8";

proc export data=abc
outfile=exprt
dbms=tab;
run;

output is

LINE_NUMBER DESCRIPTION
524JG "24PC AMEFA VINTAGE CUTLERY SET ""DUBARRY"""

so there is double quote available before and after the description here and additional doble quote is coming after & before DUBARRY word. I have no clue whats happening. Can some one help me to resolve this and make me understand what exatly happening here.

Re: SAS export giving double quote itself

RW9 — Wed, 19 Dec 2018 14:32:10 GMT

Edit, one important thing, do not name your file CSV, then use tab delimiter. CSV stands for Comma Delimited Variable file, not tab delimited!!

Yes this is standard behaviour. If you data contains quotes, or contains the delimiter, then the whole text string needs to be enclosed within quotes, and the quotes within your data need to be increased in number. If this was not the case, then reading that text string in, would say that "DUBARRY" should be read as a text string on its own (as quotes around string indicates that it contains the delimiter, even if in this case it doesn't).

You could put out the text data yourself, using a datastep, however be aware that the output file would not be valid then (I would reject data which does not follow the basic guidance on file format).

Switching to fixed width data output would also work:

Example:

https://communities.sas.com/t5/SAS-Programming/Writing-fixed-width-text-files/td-p/134975

Re: SAS export giving double quote itself

Tom — Wed, 19 Dec 2018 14:46:15 GMT

What happened? You asked for a delimited file and you got one.

Here is link to Wiki Article https://en.wikipedia.org/wiki/Comma-separated_values

Here is link to RFC 4180 that defines the standard (or as close to a standard as exists) https://tools.ietf.org/html/rfc4180

Just because you are using TAB instead of COMMA as the delimiter doesn't mean that the need to enclosed values that contain delimiters within quotes no longer exists. Which means that you also need to enclose values that contain quotes to avoid potential confusion. Which then means that you need to double up the actual quotes, again to avoid confusion. Without the quotes you could generate a file that could not be parsed.

Re: SAS export giving double quote itself

Srigyan — Thu, 20 Dec 2018 15:51:56 GMT

I don't have option to ignore the file and i have to take the tab only.

How can I solve this issue.

Re: SAS export giving double quote itself

Tom — Thu, 20 Dec 2018 16:06:38 GMT

What are you doing with the file? If you want to read it into SAS or Excel or any other software that understands how delimited files are formatted you should not have any trouble. If the software you are using does not have options for reading normally formatted delimited files then complain to the maker of that software to update their programs to be useful in the real world.

You can generate a file without the quotes by writing it yourself using a data step. You will have problems for values that contain the delimiter and also have problems with representing missing values.

data _null_;
   file 'myfile.txt' dlm='09'x ;
   set have ;
   put (_all_) (+0);
run;

If you need column headers that is not hard either.

proc transpose data=have(obs=0) out=names;
 var _all_;
run;
data _null_;
  file 'myfile.txt' dlm='09x' ;
  set names end=eof;
  put _name _ @;
  if eof then put;
run;
data _null_;
   file 'myfile.txt' dlm='09'x MOD ;
   set have ;
   put (_all_) (+0);
run;

Re: SAS export giving double quote itself

Srigyan — Fri, 04 Jan 2019 15:29:13 GMT

The application which is consume this data will be developed by the Developer and they wanted to have this input in the format where we dont have any additional double quotes. So do you think you can help me the way i wanted to get an output.

Re: SAS export giving double quote itself

Srigyan — Fri, 04 Jan 2019 15:31:29 GMT

I can manage this data without double quote as Developer going to program accrodingly, if I need to provide this data in this format. Do we have any way to do it.

Re: SAS export giving double quote itself

Tom — Fri, 04 Jan 2019 16:15:54 GMT

Personally I would push back to the "developer" to support standard data formats. If they are not then they need to provide complete specifications for how all possible data scenarios should be handled.

For now you can easily avoid have the embedded quotes protected by NOT using the DSD option when writing the file.

If you need more help show what you have tried and explain how it is not doing what you want.