topic Re: How to convert missing value to "." in SAS Programming

How to convert missing value to "."

irene_o — Sun, 09 May 2021 11:01:48 GMT

I need to output the data to text file but the missing value must be converted to "." . I have tried to use option missing but it's the same (.) as null value, thus the output still blank instead of (.) . Please help. Thanks.

temp_data:

Model Type Month_1 Month_2 Month_3 Month_4

A FIRE 123.4 5 56.7 23.86

B FIRE 2349 35.3 . .

C 234.8 . . .

text file:

A|FIRE|123.4|5|56.7|23.86

B|FIRE|2349|35.3|.|.

C||234.8|.|.|.

option missing = '.' ;

proc export data=temp_data

outfile="&path"

dbms=dlm replace;

putnames=no;

delimiter="|";

run;

Re: How to convert missing value to "."

FreelanceReinh — Sun, 09 May 2021 11:58:00 GMT

Hello @irene_o and welcome to the SAS Support Communities!

The periods for numeric missing values (and the blanks for missing character values) are suppressed by the DSD option of the FILE statement in the DATA step code that PROC EXPORT generates. Write your own DATA step to take control over the options.

Example:

data _null_;
file "&path" dlm='|';
set temp_data;
put (_all_)(:);
run;

Re: How to convert missing value to "."

Tom — Sun, 09 May 2021 15:04:35 GMT

Why? What is it that is going to read this file that does not understand that adjacent delimiters means the value is missing?

If you are writing a delimited file without a header line there is NO reason to use PROC EXPORT.

It is easier to just write the data step to create the text file yourself. In which case you a tweak the code for whatever non-standard format your text file needs.

data _null_;
  set temp_data ;
  file "&path" dlm='|';
  put (_all_) (+0);
run;

Re: How to convert missing value to "."

irene_o — Mon, 10 May 2021 02:43:14 GMT

The code works perfectly. Thanks!

Re: How to convert missing value to "."

irene_o — Mon, 10 May 2021 03:25:36 GMT

Thanks for the code! I was using Proc Report as I have another text output where null numeric is blank instead of period. Now I learn new thing using data step which is very useful.