How to export a numeric missing value to ""

[yuta_adachi]

I want to export SAS data to csv file with all fields quoted.

I tried every method listed in following site and numeric fields with missing value are exported to " " (a quoted single space) but I need missing values to be exported as "" (nothing between two quote marks).

http://www.sascommunity.org/wiki/Create_a_CSV_file_without_column_names/headers_in_row_1

As a workaround I use option missing='7f'x to export to a temp file and then export to final result by compressing the '7f'x character.

Is there any smarter method that I can get the same result?

[Reeza]

Are you using a data step to export? If so, can you post your code? 

A brute force method is

if missing(var) then put " ";

else put ...

[yuta_adachi]

I am using such a code.

options missing='';
data _null_; 
file "D:\temp\class.csv" dsd dlm=','; 
set sashelp.class; 
put (_all_) (~); 
run;
options missing='.';

I an writing the code so that the missing value is "", but it will output with " " (a quoted single byte space).

[ChrisNZ]

Because there there is no such thing as zero-length variable or format in SAS, I think you have to use @Reeza 's solution and do the processing manually, or post-process as you do now.

[Tom]

Seems like a silly requirement, but if you are willing to use two steps it is not too hard.

Let's make some test data with missing character and numeric values.

data test ;
 set sashelp.class (obs=3);
 if _n_=2 then call missing(sex);
 if _n_=1 then call missing(age);
run;

So first wrtie to a temporary file. Then read the temoporary file and "fix" the lines and re-write them. Add a comma to tbe beginning and end of the lines to make it easier. That way you can just convert both ," ", and ,".", into ,"",. You can then drop the extra commas.

filename step1 temp;
data _null_;
  set test;
  file step1 dsd dlm=',' ;
  put ',' (_all_) (~) +(-1) ',';
run;

data _null_;
  infile step1 ;
  file 'want.csv' ;
  input ;
  _infile_=tranwrd(_infile_,'," ",',',"",');
  _infile_=tranwrd(_infile_,',".",',',"",');
  _infile_=substr(_infile_,2,length(_infile_)-2);
  put _infile_;
run;

[yuta_adachi]

Thank you for your response.

As a result I decided to use two steps. First I export to csv file by replacing missing values with characters that I do not use, and then deleted that character.

options missing='7f'x;
data _null_; 
file "D:\temp\baseball.csv" dsd dlm=','; 
set sashelp.baseball; 
put (_all_) (~); 
run;
options missing='.';

data _null_;
infile "D:\temp\baseball.csv";
file "D:\data\baseball.csv";
input;
_infile_=compress(_infile_,'7f'x);
put _infile_; 
run;

[Reeza]

What about using a space or similar as your hex character or will that interfere with your processing down the line?

[yuta_adachi]

Because blank space is not accepted as a numeric field in a specification of outside system, I am trying to export csv file without a space between two quote marks.

Does this answer match the intention of the question?