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 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++