Date:         Wed, 12 Mar 2008 12:16:46 -0400
Reply-To:     Michael Raithel <michaelraithel@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael Raithel <michaelraithel@WESTAT.COM>
Subject:      Re: How to import an Excel Spreadsheet as ALL character fields??
In-Reply-To:  <015501c88455$f7ca3550$c12fa8c0@HP82083701405>
Content-Type: text/plain; charset="us-ascii"

Dear SAS-L-ers,

Mary posted the following:

>
> I'm trying to import data in which numeric columns sometimes
> have characters in them, such as 3?, or 69-70.   I would like
> to import the sheet as all characters first so I can run
> frequencies on the sheet and get a better idea of how to adjust them.
>
> I'm using the standard Excel import, like this:
>
> PROC IMPORT OUT= WORK.data_master
>
> DATAFILE=
> "C:\Work_Activities\spreadsheet_conversions\DNA_Plate_Data_Mas
> ter_revised.xls"
>
> DBMS=EXCEL REPLACE;
>
> SHEET="'Data Master$'";
>
> GETNAMES=YES;
>
> MIXED=NO;
>
> SCANTEXT=YES;
>
> USEDATE=YES;
>
> SCANTIME=YES;
>
> RUN;
>
> I even tried adding 12 rows with all *** in every field, like this:
>
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       ####################
>       1
>       1
>       1
>       3

> But I'm still getting all missings for that column, followed
> by the valid numbers, but missings for all the character numbers.
>
> How can I read in the Excel spreadsheet as ALL character fields?
>
Mary, great description of your problem.  Simply change your code, above
to MIXED=YES.  That will bring the mixed-case Excel columns in as text.

The best paper on working with SAS/Excel problems is fellow Westatian Ed
Heaton's SUGI 31 paper:

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

I keep a copy of it handy for those times when I really need to jam on
an Excel spreadsheet.

Mary, best of luck showing Excel who is the boss!


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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
All lies and jest, still, a man hears what he wants to hear
and disregards the rest. - Simon and Garfunkel, The Boxer
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++