topic Re: How do I export to CSV without quotes for null values in SAS Procedures

How do I export to CSV without quotes for null values

datakat — Tue, 23 Jul 2019 20:04:02 GMT

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"

Re: How do I export to CSV without quotes for null values

ballardw — Tue, 23 Jul 2019 22:27:15 GMT

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

Re: How do I export to CSV without quotes for null values

datawhisper — Tue, 23 Jul 2019 23:57:02 GMT

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

Re: How do I export to CSV without quotes for null values

Tom — Wed, 24 Jul 2019 01:34:53 GMT

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;

Re: How do I export to CSV without quotes for null values

datakat — Wed, 24 Jul 2019 03:28:55 GMT

Thanks for the wallpaper code! That did the trick.

Re: How do I export to CSV without quotes for null values

Tom — Wed, 24 Jul 2019 03:31:24 GMT

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

Re: How do I export to CSV without quotes for null values

datakat — Wed, 24 Jul 2019 03:38:06 GMT

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

Re: How do I export to CSV without quotes for null values

Tom — Wed, 24 Jul 2019 03:48:37 GMT

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