DATA Step, Macro, Functions and more

How to export a numeric missing value to ""

Reply
New Contributor
Posts: 4

How to export a numeric missing value to ""

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?

Super User
Posts: 19,878

Re: How to export a numeric missing value to ""

Posted in reply to yuta_adachi

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

New Contributor
Posts: 4

Re: How to export a numeric missing value to ""

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

PROC Star
Posts: 1,760

Re: How to export a numeric missing value to ""

Posted in reply to yuta_adachi

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.

Super User
Super User
Posts: 7,083

Re: How to export a numeric missing value to ""

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;

 

New Contributor
Posts: 4

Re: How to export a numeric missing value to ""

Posted in reply to 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;
Super User
Posts: 19,878

Re: How to export a numeric missing value to ""

Posted in reply to yuta_adachi

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

New Contributor
Posts: 4

Re: How to export a numeric missing value to ""

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?

Ask a Question
Discussion stats
  • 7 replies
  • 178 views
  • 0 likes
  • 4 in conversation