How do I export to CSV without quotes for null values

[datakat]

I have a dataset in SAS and I need that data in a csv file with parenthesis around the data in each field, unless it's null, then I need it to be completely empty.

I'm using the following code to add the quotations:

data _null_;
file 'D:file/location/Data_Want.csv' dsd dlm=',';
set Data.Have ;
if _n_ = 1 then put  VAR1 ~ VAR2~ VAR3~ VAR4~ 
;
run;

But when it's null it's showing "." instead of leaving it blank as it had prior to adding the quotations. I've searched and had no luck finding a resolution. Any suggestions would be much appreciated!

I'm getting this: ".",".",".","12"

When I need this:  ,,,"12"

[ballardw]

The period is showing because that is the default display character for a missing value.

You can get single blank by using:

Options missing=" ";

prior to running that code.

Better would  be to use Proc Export or the data export wizard to create CSV files but numeric values do not normally have quotes and quotes would normally only be required for character values that contain commas in delimited files.

If that is not acceptable then you are likely going tto

And why do you say "with parenthesis around the data in each field, unless it's null," but show quotes???

[datawhisper]

I would like to export a CSV file from a SAS dataset in which the text and date variables are enclosed in quotes when there is a value present and not when the value is null.

For example, below, Jane does not have a middle name or a birth date, and so no "" appear around the nulls

"First_Name","Middle_Name","Last_Name","Birth_Date"

"Jeff","Duke","Doe","1/1/2000"

"Jane",,"Doe",

All I can manage to get in a CSV file from SAS is:

"First_Name","Middle_Name","Last_Name","Birth_Date"

"Jeff","Duke","Doe","1/1/2000"

"Jane","","Doe",""

Using:

data _null_;
file 'D:file/location/Data_Want.csv' dsd dlm=',';
set Data.Have ;
if _n_ = 1 then '"First_Name","Middle_Name","Last_Name","Birth_Date"';

put  First_Name ~ Middle_Name ~ Last_Name ~ Birth_Date ~ ;
run;

Is there a way to do this so that the Middle_Name and Birth_Date fields will export without quotes when null? TIA

[Tom · Accepted solution]

Have you pushed back against this (bogus) request to add the unneeded quotes around every value?

What is the source of that strange requirement?

Are you loading the file into some software package?  Can't the software package read a normal CSV file? What software package is it that can't read a normal CSV file?

If you really have to then just generate some wallpaper code to test each variable's value for missing and use a different PUT statement.

data test;
  input a b $ c ;
cards;
1 red 2
. blue 3
4 . 5
;

data _null_;
  set test;
  file log dsd ;
  if missing(a) then put a @; else put a ~ @;
  if missing(b) then put b @; else put b ~ @;
  if missing(c) then put c @; else put c ~ @;
  put;
run;

[datakat]

Thanks for the wallpaper code!  That did the trick.

[Tom]

---

@datakat wrote:

Thanks for the wallpaper code!  That did the trick.

---

What happens if you just give them a normal CSV file without the unneeded quotes?

Quotes only need to be added to values that contain the delimiter character or actual quote character.

[datakat]

We get errors without the quotes. Not sure why yet. 

[Tom]

---

@datakat wrote:

We get errors without the quotes. Not sure why yet. 

---

What software are you using? It a known product (like R, Redshift, SPSS, SQL/Server, etc.)? Or some homemade tool?