topic SAS to csv outputs extra quotation marks in SAS Data Management

SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 18:03:36 GMT

Whenever I convert SAS to csv, I noticed that it would output extra quotation marks if a value contains quotation marks.

For example:

Original value:

hello "M" world

Output to CSV value:

"hello""M""world"

Here is my code to convert from SAS to CSV. I expect it has something to do with my put statement?

%Let outfile = '\path\to\save\csv\table.csv' DLM = '|' DSD;


DATA _NULL_;
  SET library.table;
  FILE &outfile;
  put (_ALL_) (:);
RUN;

Any help would be great, Thanks!

Re: SAS to csv outputs extra quotation marks

ballardw — Fri, 09 Nov 2018 18:20:43 GMT

@jim_toby wrote:

Whenever I convert SAS to csv, I noticed that it would output extra quotation marks if a value contains quotation marks.

For example:

Original value:

hello "M" world

Output to CSV value:

"hello""M""world"

Here is my code to convert from SAS to CSV. I expect it has something to do with my put statement?
%Let outfile = '\path\to\save\csv\table.csv' DLM = '|' DSD;


DATA _NULL_;
  SET library.table;
  FILE &outfile;
  put (_ALL_) (:);
RUN;
Any help would be great, Thanks!

First thing CSV = Comma Separated Values. If you use DLM='|' or anything except a comma the file is not csv. This does have serious potential for causing problems with programs that expect a CSV file to have commas.

I get different results:

Hello,"""M""",world

Since your post of the example output does not include any delimiter than I have to assume the post is from a program that modified the result and so the quotes around the words hello and world were placed by that other program.

Note that when checking the output of delimited file do not open them with anything except a plain text editor such as NotePad or even the SAS editor. Spreadsheets are almost always going to change something, especially if you then save from the spreadsheet.

One might ask why you thought you needed quotes around a variable to begin with?

Very simple to remove quotes with the COMPRESS function.

Re: SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 18:32:49 GMT

@ballardw my example was just one value surrounded by pipes.

So it would look like: |hello "M" world|....|.......

And I did not construct this table so I don't want to change any of the data inside of it. Therefore I would like to leave the quotes

Re: SAS to csv outputs extra quotation marks

data_null__ — Fri, 09 Nov 2018 18:50:16 GMT

https://en.wikipedia.org/wiki/Comma-separated_values

Any field may be quoted (that is, enclosed within double-quote characters). Some fields must be quoted, as specified in following rules.

"1997","Ford","E350"

Fields with embedded commas or double-quote characters must be quoted.

1997,Ford,E350,"Super, luxurious truck"

Each of the embedded double-quote characters must be represented by a pair of double-quote characters.

1997,Ford,E350,"Super, ""luxurious"" truck"

Re: SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 19:04:29 GMT

@data_null__

See this issue: https://communities.sas.com/t5/SAS-Procedures/Proc-Export-adding-extra-quotation-marks/td-p/90021

He was able to solve it by doing this:

DATA _null_;
  SET SASHelp.table;
  FILE &outfile;
  put (_ALL_) ('|');
RUN;

So this would indeed get rid of the extra quotations but it also adds the pipe delimeter at the start of every row. Main point is, is that it doesn't add the extra quotations.

Re: SAS to csv outputs extra quotation marks

Tom — Fri, 09 Nov 2018 19:04:39 GMT

I don't see any extra quotes. Just the required quotes needed to make sure that the resulting file can actually be converted back to the source data.

Re: SAS to csv outputs extra quotation marks

Reeza — Fri, 09 Nov 2018 19:08:57 GMT

You can remove the quotes, but the person reading the file is going to hate you.

Re: SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 19:14:35 GMT

@Reeza I want to convert it from SAS to csv in order to load it to netezza. If it adds extra quotations, it will appear with extra quotations in Netezza. So I would like for it to not manipulate any of the values.

Re: SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 19:15:41 GMT

@Tom I want to convert it from SAS to csv in order to load it to netezza. If it adds extra quotations, it will appear with extra quotations in Netezza. So I would like for it to not manipulate any of the values. I don't have plans to convert it back to the source data since I'm not actually manipulating the SAS table but rather creating a new csv file.

Re: SAS to csv outputs extra quotation marks

Reeza — Fri, 09 Nov 2018 19:22:12 GMT

Except for the CSV standard (which you're using pipes for some reason) requires double quotes. Most apps reading would expect it as well. Is Netezza adding the extra quotes?

For example, I tried putting your text into Excel and then saved it as a CSV, it looks like this:

"hello ""M"" world"

And then when I read it back in, it's fine. I'd be really really surprised if Netezza didn't do the same thing. Any particular reason you're not uploading straight to Netezza? Assuming no connection for some reason.

@jim_toby wrote:

@Reeza I want to convert it from SAS to csv in order to load it to netezza. If it adds extra quotations, it will appear with extra quotations in Netezza. So I would like for it to not manipulate any of the values.

Re: SAS to csv outputs extra quotation marks

Tom — Fri, 09 Nov 2018 19:25:37 GMT

@jim_toby wrote:

@Tom I want to convert it from SAS to csv in order to load it to netezza. If it adds extra quotations, it will appear with extra quotations in Netezza. So I would like for it to not manipulate any of the values. I don't have plans to convert it back to the source data since I'm not actually manipulating the SAS table but rather creating a new csv file.

So you need to ask Netezza what rules it uses for parsing text files. A number of databases I have used behave as if they were C code or Unix shell commands and expect to see backslashes in the data to "escape" special characters.

But most of them also have options so that they can handle normal CSV files (as defined by the standard that @data_null__ provided the link for). For example if you are using the Redshift COPY command you can add the CSV option to have it handle quoted strings properly.

Re: SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 19:27:27 GMT

@Reeza I'm not loading the file straight from SAS because the table contains over 500 million records and I get a communication link error when I do a bulkload due to the inactivity timeout limit

Re: SAS to csv outputs extra quotation marks

jim_toby — Fri, 09 Nov 2018 19:31:38 GMT

@Tom Sounds good, I'll do that. Thanks!

Re: SAS to csv outputs extra quotation marks

Tom — Fri, 09 Nov 2018 19:54:02 GMT

One thing to try is to not use the DSD option on the FILE statement.

This will mean that you are not protected from embedded delimiters or unbalanced quotes. So you need to check your data before writing it.

It also will means that missing (aka NULL) values will be represented as a single character instead of by adjacent delimiters. So you need check to how Netezza will lines like this:

value1| |value3

instead of like this:

value1||value3

A lot of databases will allow you to include the work "null" (or perhaps some other character string) to indicate missing values. If so you might be able to use user defined formats to fix that.

data have;
  length yr 8 make model $20 description $100 ;
  yr=1997;
  make='Ford';
  model='E350';
  do description = ' ',"Super, luxurious truck","Super, ""luxurious"" truck" ;
    output;
  end;
  call missing(yr);
  output;
run;
proc format ;
  value null low-high=[best32.] other='null';
  value $null  ' '='null' other=[$200.];
run;

filename csv temp;
data _null_;
  file csv dlm='|' ;
  set have;
  format _numeric_ null. _character_ $null. ;
  put (_all_) (:) ;
run;
data _null_;
  infile csv ;
  input;
  list;
run;

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7
1         1997|Ford|E350|null 19
2         1997|Ford|E350|Super, luxurious truck 37
3         1997|Ford|E350|Super, "luxurious" truck 39
4         null|Ford|E350|Super, "luxurious" truck 39

Re: SAS to csv outputs extra quotation marks

Ksharp — Sat, 10 Nov 2018 11:06:30 GMT

Did you try PROC EXPORT or macro %DS2CSV() ?