Date:         Tue, 14 Jan 2003 09:42:47 -0700
Reply-To:     William Kossack <kossackw@NJC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         William Kossack <kossackw@NJC.ORG>
Organization: Posted via Supernews, http://www.supernews.com
Subject:      Re: Problem to export dataset to Excel file when having missing
              value
Content-Type: text/plain; charset=us-ascii

I just tried this on my spreadsheet and got the same results.  Proc Import is
totally stupid in its handling of missing data.

I gets lots of data with missing values and I've had to resort to saving
spreadsheets as csv inorder to read them correctly.  apparently import only
looks at the first few records to decide what a field is and if you happen to
have more than 15 missing at the beginning of a spreadsheet your screwed.

Yu Guo wrote:

>         Thank you, everybody! I have update my windows 2000 from SP2 to SP3,
> which resolved this problem.
>         Here are relevant links in case someone need:
>
>         The relevant article on microsoft support network:
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;294410
>
>         NOTE: The Jet 4.0 SP6 files are included with Windows 2000 Service
> Pack 3 (SP3).
>          You do not have to install this update if you have already
> installed
>         Windows 2000 SP3.
>
>         The link for downloading win2000 servicepack 3:
>
> http://www.microsoft.com/windows2000/downloads/servicepacks/sp3/default.asp
>
>         Thanks!
>
>         Raymond
>
>         From:   Carol Gosselin <clgossel@gw.fis.NCSU.EDU> on 01/09/2003
> 08:16 AM
>         To:     Yu Guo/SPEN/ScottLevin@Informatics
>         cc:
>
>         Subject:        Re: Problem to export dataset to Excel file when
> having missing value
>
>         I had this problem on Windows 2000 a while back.  SAS told me that
> Microsoft has a fix for this problem.  Go to the Microsoft web site.  Ther
> correct fix number to request is KB doc Q294410.  Hope this helps.
>
>         Carol L. Gosselin
>         Applications Analyst Programmer II
>         University Planning and Analysis
>         Box 7002, 201 Peele Hall
>         North Carolina State University
>         Raleigh, NC 27695-7002
>         Phone:  919-515-6435
>         Fax:  919-831-3541
>         Email:  carol_gosselin@ncsu.edu <mailto:carol_gosselin@ncsu.edu>
>
>         >>> Yu Guo <Yu.Guo@VERISPAN.COM> 01/08/03 06:11PM >>>
>         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;