Date:         Wed, 8 Jan 2003 19:31:44 -0800
Reply-To:     Bill McKirgan maxsfolks <bill-mckirgan@UIOWA.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Bill McKirgan maxsfolks <bill-mckirgan@UIOWA.EDU>
Organization: http://groups.google.com/
Subject:      Re: Problem to export dataset to Excel file when having missing
              value
Content-Type: text/plain; charset=ISO-8859-1

Raymond,

I tried running your code and everything seems to work fine.
Perhaps I misunderstood the problem.  I've copied and pasted the data
from excel below.  As you can see, nothing was shifted to the left to
fill the
missing values.

Sorry I don't know why the contents of cells might shift
to fill cells with missing values.

Bill




test1.xls
c1      c2      c3      c4      c5      c6
1               3       4       5       6
11      12      13      14      15      16

test2.xls
c1      c2      c3      c4      c5      c6
1       2               3       5       6
11      12      13      14      15      16



Yu.Guo@VERISPAN.COM (Yu Guo) wrote in message news:<200301082310.h08NAdO10335@listserv.cc.uga.edu>...
> Hello,
>
> I met a problem when I tried to export a dataset to Excel file
> when having missing value. In the excel file, I suppose the missing
>  value should either occupied an empty cell or show '.' in the cell.
> But in fact, the cell respected to the missing value just disappeared so
> that
> the data on the left side will shift one cell left. Below is an example for
> this
> problem. In test1.xls an empty cell is generated for the missing value,
> while
> in test2.xls, the missing value disappeared and data shift left for one
> cell.
> Does anybody know how to let it export missing data correctly to EXCEL file?
>
> Thanks!
>
> -Raymond
>
>
> data test1;
> input c1  c2  c3  c4 c5 c6;
> cards;
> 1 . 3 4 5 6
> 11 12 13 14 15 16
> run;
>
>
> data test2;
> input c1  c2  c3  c4 c5 c6;
> cards;
> 1 2 . 3 5 6
> 11 12 13 14 15 16
> run;
>
> PROC EXPORT
>         DATA = test1
>         OUTFILE = "test1.xls"
>         DBMS = EXCEL
>         REPLACE;
> run;
>
>
> PROC EXPORT
>         DATA = test2
>         OUTFILE = "test2.xls"
>         DBMS = EXCEL
>         REPLACE;
> run;