Date:         Wed, 14 Nov 2007 13:16:07 -0500
Reply-To:     Michael Raithel <michaelraithel@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael Raithel <michaelraithel@WESTAT.COM>
Subject:      Re: Importing mixed datatype fields into SAS
In-Reply-To:  <9b5abea60711140849u77ef34b1rb0839f6f939f91b0@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"

Dear SAS-L-ers,

souga soga posted the following:

> I am trying to import excel data into SAS using proc import .
> One of the fields has a mixed datatype i.e has numeric and
> text data which i would like to import as character. This is
> what i am using which doesn't work, it takes the mixed data
> type field and converts it to numeric and puts missing in the
> place of text.
>
> *
>
> PROC* *IMPORT* OUT= WORK.TEST
>
> DATAFILE= "XXXXAnalysis.xls" DBMS=EXCEL REPLACE;
>
> SHEET="'YYYY$'";
>
> GETNAMES=Yes;
>
> MIXED=YES;
>
> range = "A6:K138";
> *
>
> RUN*;
>
> All help is much appreciated as always.
>
Souga, the problem is that SAS uses the Excel Jet engine and checks the
first 8 rows to determine the data type of an Excel column when creating
a SAS variable from it.  If it finds only characters, then it becomes
character variable; if it finds mixed numbers and characters, it becomes
a character variable; if it finds only numerics, then it becomes a
numeric variable---AND: any character values in rows 9 - infinity become
missing values.  Argh!  Sound familiar?!?!?

This problem and the workaround are covered in fellow Westatian Ed
Heaton's very popular, award-winning SUGI 31 paper:

So, Your Data Are in Excel!
http://www2.sas.com/proceedings/sugi31/020-31.pdf

The workaround is not for the faint of heart, as it calls for modifying
something in the Windows registry.  But, you strike me as a bold person,
so check it out!

Souga, best of luck to you in resolving your Excel data issues!

I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not
reflect those of my organization or my associates. All SAS code and/or
methodologies specified in this posting are for illustrative purposes
only and no warranty is stated or implied as to their accuracy or
applicability. People deciding to use information in this posting do so
at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance"
E-mail: MichaelRaithel@westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments,
Second Edition
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Electricity is actually made up of extremely tiny particles
called electrons, that you cannot see with the naked eye
unless you have been drinking. - Dave Barry
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++