PROC EXPORT produces "0" for blank or null values

[Q1983]

data have;

format mydate date9.;

input ln_no $ state $ mydate date9.;

datalines;

1123 AL 1jun2018

1123 AZ

;

run;

data have2;

set have;

format date_yr_mth yymmd7.;

date_yr_mth = mydate;

date_yr_mth1 = put(date_yr_mth,yymmd7.);

run;

PROC EXPORT Outfile= "\\MyDrive\have3.xlsb"

DATA= have2

DBMS= EXCELCS REPLACE;

SHEET='Details';

SERVER='server1';

Note the second value AZ has no date it is a blank.  When it exports it somehow gets a .  When it runs through our sharepoint server it is assigned a "0"  I tried something like this in the datastep if date_yr_mth = '0' or date_yr_mth = . then date_yr_mth = ' ';  Yet it still assigns a . or '0' when it runs.  Is there a way in the proc export itself to assign all null values as a true null.  I do not want it to display anything in the field.

[Reeza · Accepted solution]

Its not the export, check your HAVE2 data set. It has the periods as well.

Also, you may have the following set, that you'll need to reset:

option missing='0';

[Kurt_Bremser]

If you want control of your process and have things done your way,

TAKE CONTROL!

This specifically precludes the use of something as crappy as .xlsb for data transfer.

Do this:

data have;
format mydate date9.;
input ln_no $ state $ mydate date9.;
datalines;
1123 AL 1jun2018
1123 AZ
;
run;

options missing = '';

data _null_;
set have;
file '/$HOME/sascommunity/out.csv' dlm=',' dsd;
if _n_ = 1 then put 'ln_no,state,mydate';
put
  ln_no
  state
  mydate
;
run;

instead.

Since xlsb involves a Microsoft-supplied piece of software along the way, you'll never know what happens in there. Mostly something that will curl your toenails.