topic Re: How to remove double quotes from a proc export CSV output in SAS Programming

How to remove double quotes from a proc export CSV output

chimukah — Mon, 28 Sep 2020 19:46:24 GMT

Good evening all,

Please i have an output file from proc export to a csv. But the output variables have double quotes like

"var1";"var2";"var3";"var4" and so on..

I need to remove these double quotes in SAS using proc export or any means so that the output will not have any quote.

Kindly help

Chimukah

Re: How to remove double quotes from a proc export CSV output

Reeza — Mon, 28 Sep 2020 20:07:54 GMT

That's typically done for character variables in case they have a comma value in them so that they can be read correctly. It's usually the correct way to format and transfer a CSV file. If you'd like to override that, well, it's not really the standard definition of a CSV but it can be done.

You'll need to use a data step and manually code it yourself though.

Re: How to remove double quotes from a proc export CSV output

chimukah — Mon, 28 Sep 2020 20:23:28 GMT

You said i need to manually code it. Is it before the Proc export procedure or after the Proc export?

Re: How to remove double quotes from a proc export CSV output

ballardw — Mon, 28 Sep 2020 21:17:19 GMT

@chimukah wrote:

You said i need to manually code it. Is it before the Proc export procedure or after the Proc export?

Instead of Proc Export. Proc export has very few options controlling such behavior.

One question is WHY are the quotes objectionable? The purpose of Proc Export is generally to exchange data and quotes around text fields in comma separated values are comma and necessary if you have values containing commas.

Before you go too far with this project have you verified that absolutely none of your variables have a comma as part of the value?

Re: How to remove double quotes from a proc export CSV output

Reeza — Mon, 28 Sep 2020 21:46:14 GMT

Instead of PROC EXPORT - you cannot use PROC EXPORT you need to use a manual data step.

data _null_;
set sashelp.class;
filename 'outputfile.txt';
put name "," age "," gender;
run;

Re: How to remove double quotes from a proc export CSV output

Tom — Tue, 29 Sep 2020 05:41:55 GMT

The premise of your question is wrong. PROC EXPORT does not add any extra quotes to CSV files.

Test it yourself.

filename csv temp;
proc export data=sashelp.class(obs=3) file=csv dbms=csv replace; run;

212   data _null_;
213     infile csv ;
214     input;
215     put _infile_;
216   run;

NOTE: The infile CSV is: ...

Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
NOTE: 4 records were read from the infile CSV.
      The minimum record length was 18.
      The maximum record length was 26.

Re: How to remove double quotes from a proc export CSV output

chimukah — Tue, 29 Sep 2020 05:49:06 GMT

HI Tom,
Did you check the result of your output using notepad to confirm that there is no double quotes on the variable names?

Just wants to be sure that your process is correct
Thanks

Re: How to remove double quotes from a proc export CSV output

Tom — Tue, 29 Sep 2020 05:52:56 GMT

@chimukah wrote:
HI Tom,
Did you check the result of your output using notepad to confirm that there is no double quotes on the variable names?

Just wants to be sure that your process is correct
Thanks

Looking at the file with notepad is not going to look any different than looking at it with an INPUT statement.

Please show an example of the dataset you have and the code you used to create the file. You can use a similar simple data step to read the file back in to see what it looks like.

Re: How to remove double quotes from a proc export CSV output

Tom — Tue, 29 Sep 2020 06:07:08 GMT

PROC EXPORT with DBMS=CVS not going to add any quotes, UNLESS the field value (including column header value) require them.

Quotes are required when the value contains the delimiter or the quote character itself. Without the quotes the file could not be re-read since there would be no way to tell the difference between the delimiters in the values and the delimiters between the values.

And once you have the possibility of quoted values you need to add quotes around values that have quotes to prevent the quotes in the values from looking like quotes added to protect delimiters.

Re: How to remove double quotes from a proc export CSV output

chimukah — Tue, 29 Sep 2020 06:27:49 GMT

Hi Tom,
In this case a delimiter is required. The header should be without quotes. Normally if you view the output in maybe Excel you will not see the quotes, but if you try viewing it from Notepad you will observe that double quotes are embedded in all the colunm names variable names.
Tnx

Re: How to remove double quotes from a proc export CSV output

Kurt_Bremser — Tue, 29 Sep 2020 06:55:22 GMT

@chimukah wrote:
... but if you try viewing it from Notepad you will observe that double quotes are embedded in all the colunm names variable names.

Which is perfectly OK. Applications that read CSV files correctly into tables (like you experienced with Excel) will remove the quotes when converting the header line to column names. Notepad shows you the raw file, which is good for diagnosis.

Re: How to remove double quotes from a proc export CSV output

Tom — Tue, 29 Sep 2020 16:37:00 GMT

@chimukah wrote:
Hi Tom,
In this case a delimiter is required. The header should be without quotes. Normally if you view the output in maybe Excel you will not see the quotes, but if you try viewing it from Notepad you will observe that double quotes are embedded in all the colunm names variable names.
Tnx

I understand that some programs will transform files when reading them. Which is why in the example I posted already I shared the actual text of the file that PROC EXPORT generated so you could see with your own eyes that it DID NOT add any quotes around the column headers.

Here is again where I used DELIMITER=';' statement to use semi-colon instead of comma as the delimiter.

265   data _null_;
266     infile csv ;
267     input;
268     put _infile_;
269   run;

NOTE: The infile CSV is: Filename=...

Name;Sex;Age;Height;Weight
Alfred;M;14;69;112.5
Alice;F;13;56.5;84
Barbara;F;13;65.3;98
NOTE: 4 records were read from the infile CSV.
      The minimum record length was 18.
      The maximum record length was 26.

As you can clearly see there are no quotes in the 4 lines of text that PROC EXPORT generated to export the first three observations from SASHELP.CLASS.

So please post an example of code that you have run where PROC EXPORT did add quotes.

Re: How to remove double quotes from a proc export CSV output

Ksharp — Tue, 29 Sep 2020 11:57:56 GMT

Try another engine.
proc export data=sashelp.class file=csv dbms=dlm replace; delimiter=','; run;

Re: How to remove double quotes from a proc export CSV output

Kurt_Bremser — Tue, 29 Sep 2020 12:44:55 GMT

With DBMS=CSV, there will also not be quotes around the column names.

Re: How to remove double quotes from a proc export CSV output

chimukah — Tue, 29 Sep 2020 15:54:35 GMT

Let me also add that i am working with SAS studio. Could it be the problem. I have colleagues who uses different SAS platform and he is not having issues with proc export to CSV

Thank you

Re: How to remove double quotes from a proc export CSV output

Kurt_Bremser — Tue, 29 Sep 2020 16:05:44 GMT

POST.YOUR.CODE.

Re: How to remove double quotes from a proc export CSV output

Reeza — Tue, 29 Sep 2020 16:07:07 GMT

SAS Studio 3.8

SAS 9.4M6

PROC EXPORT - data has no embedded delimiters.

PROC EXPORT, with embedded delimiters (demo2.csv)

Re: How to remove double quotes from a proc export CSV output

Reeza — Tue, 29 Sep 2020 16:11:53 GMT

Different versions may have different specifications, that being said, what you're describing is not the default SAS behaviour many of us would expect so something else is the issue.

What happens if you run the exact same code I did, replacing the path to something that makes sense for you?

proc export data=sashelp.class outfile='/home/fkhurshed/demo.csv' 
dbms=csv 
replace; 
run;

data class;
set sashelp.class;

if name in ('Alfred', 'Jane') then name = 'My, Name';
run;


proc export data=class outfile='/home/fkhurshed/demo2.csv' 
dbms=csv 
replace; 
run;

*check versions;
proc product_status;run;

Re: How to remove double quotes from a proc export CSV output

chimukah — Tue, 29 Sep 2020 16:38:41 GMT

Let me demonstrate a little :
data b1;
do sample = 1 to 15;
do unit = 1 to 3;
if unit = 1 then seq = "A";
else if unit = 2 then seq = "B";
else if unit = 3 then seq = "C";
output;
end;
end;
run;

proc export data = b1 outfile = "C:\Users\Documents\My SAS Files\9.4/My_Programmes\ran.csv" dbms=dlm label replace;
delimiter = ';'; run;

Re: How to remove double quotes from a proc export CSV output

chimukah — Tue, 29 Sep 2020 16:46:54 GMT

data b1;
        do sample = 1 to 15;
            do  unit = 1 to 3;
               if unit = 1 then seq = "A";
               else if unit = 2 then seq = "B";
               else if unit = 3 then seq = "C";
            output;
        end;
  end;
run;

 

proc export data = b1 outfile = "C:\Users\Documents\My SAS Files\9.4\
My_Programmes\ran.csv" dbms=dlm label replace;

delimiter = ';'; run;