Date: Tue, 26 May 2009 11:12:41 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: Is this a bug? Reading from Excel files with blanks
In-Reply-To: <28601163.1243352911084.JavaMail.root@mswamui-andean.atl.sa.earthlink.net>
Content-Type: text/plain; charset=ISO-8859-1
Excel only reads down a certain distance to determine if a column is a
number or character. If it fails to find a number by a certain point, it
will decide that the column is a character column. However, in Excel, if
you have numerics, unless you set MIXED=YES, it will consider those numerics
to be missing values when it reads that column in as char.
You need to either edit your registry (in 9.1 it's under the JET engine, in
9.2 it's under ACE, access connectivity engine) to increase the
TYPEGUESSROWS key (HKLM\Software\Microsoft\ and then either Jet\ or
Office\12.0\Access Connectivity Engine\; search on SAS-L for more
comprehensive instructions beyond that point, and as usual, beware editing
the registry, as it can be dangerous); that will cause SAS (via the JET/ACE)
to look further down the column before it gives up and declares it a CHAR,
or instead use MIXED=YES which will cause it to be a TEXT field, but the
numbers will appear (formatted as text).
Finally, not sorting it so missing rows are at top will fix the problem as
well.
-Joe
On Tue, May 26, 2009 at 10:48 AM, Peter Flom <
peterflomconsulting@mindspring.com> wrote:
> Hi all
>
> A client recently gave me an Excel file, with some blanks. I imported it
> with an
> IMPORT statement created by SAS:
>
> <<<
> PROC IMPORT OUT= WORK.neel
> DATAFILE= "C:\Analysis\Neel Shah\Vertebroplasty2.xls"
> DBMS=EXCEL REPLACE;
> RANGE="Data$";
> GETNAMES=YES;
> MIXED=NO;
> SCANTEXT=YES;
> USEDATE=YES;
> SCANTIME=YES;
> RUN;
> >>>>>
>
>
> Later, he told me that the blanks should be 0.
>
> No problem ... a very simple recode
>
> <<<
> if var = . then var = 0;
> >>>>
>
> BUT, in trying to figure out why there were a lot of missings, I sorted (in
> Excel) on the variable that had blanks.
> This put the blanks at the top. Then, SAS reported that ALL of the subjects
> had missing for that variable!
>
> When I went back to Excel and substituted a 0 for the first missing,
> everything was fine again.
>
> So, no problem to solve, but ..... this could be hard to find!
>
>
> Peter
>
> Peter L. Flom, PhD
> Statistical Consultant
> www DOT peterflomconsulting DOT com
>
| 
