How to remove double quotes from a proc export CSV output

[chimukah]

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

[Reeza]

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.

[chimukah]

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

[ballardw]

---

@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?

[Reeza]

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;

[Tom]

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.

[chimukah]

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

[Tom]

---

@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.

[Tom]

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.

[chimukah]

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

[Kurt_Bremser]

---

@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.

[Tom]

---

@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.

[Ksharp]

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

[Kurt_Bremser]

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

[chimukah]

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