Edzard,

Sounds like another Excel import problem that was posted earlier in the week, namely that SAS isn't reviewing enough records to determine that a column has mixed data. In your case, it might even be that one worksheet has just numeric data for a particular column, while another has either alphanumeric or mixed.

Two solutions were proposed, if I recall correctly, one changing the guessingrows setting in the registry, and another adding a dummy first row to each spreadsheet (with alpha characters) to force SAS to expect mixed data in the problematic columns.

HTH, Art ---------- On Sat, 14 Jan 2006 11:10:14 -0500, Edzard van Santen <evsanten@ACESAG.AUBURN.EDU> wrote:

>Paul, >Thanks for the suggestion. It's slick but I am getting the following error >message: "Variable XYZ has been defined as both character and numeric". The >cause of the problem are missing obs in the source file that were coded with >the standard period. I thought I could solve the problem by including >MIXED=YES into the libname statement but that didn't work. Any suggestions. > >EvS > > > >On Fri, 13 Jan 2006 15:16:09 -0800, Choate, Paul@DDS <pchoate@DDS.CA.GOV> wrote: > >>Edzard - >> >>I'd use a libname excel engine and a single datastep to read the files. >> >>libname VT 'C:\Corn\'; >>libname exbk excel 'C:\Corn\Corndat05.xls' ; >> >>DATA vt.Corn; >> SET exbk.'Start$'n exbk.'TVS_REG$'n exbk.'SMS_REG$'n >> exbk.'TVS_IRR$'n exbk.'PEF_REG$'n exbk.'EVS_NOT$'n >> exbk.'BEF_REG$'n exbk.'GCS_REG$'n exbk.'WGS_REG$'n >> exbk.'WGS_IRR$'n exbk.'SMS_PRE$'n exbk.'SMS_EAR$'n >> exbk.'EVS_EAR$'n exbk.'GCS_EAR$'n ; >>run; >>libname exbk clear; >> >> >>I might create the list in the set statement with some sort of macro. >>It depends on whether the list is variable or static. >> >>hth >> >>Paul Choate >>DDS Data Extraction >>(916) 654-2160 >> >>-----Original Message----- >>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >>Edzard van Santen >>Sent: Friday, January 13, 2006 3:00 PM >>To: SAS-L@LISTSERV.UGA.EDU >>Subject: Re: Why not use macros ? >> >>OK folks, >>Look at the following simple (simplistic) macro I use to take care of >>repetitive business. I am ready and willing to learn how to do it >>better. >> >>EvS >> >>options nocenter ps=5000 ls=240 ; >>libname VT 'C:\Corn\'; >>proc import datafile='C:\Corn\Corndat05.xls' >> out=vt.Corn dbms=excel2000 replace; >> sheet = "Start"; >>run; >>%macro import(sheet); >>proc import datafile='C:\Corn\Corndat05.xls' >> out=import dbms=excel2000 replace; >> sheet = "&sheet"; >>proc datasets nolist; >> append base=vt.Corn data=import force; >>run; >>%mend import; >>* this list contains the worksheet names to be imported and appended ; >>%import(TVS_REG); >>%import(SMS_REG); >>%import(TVS_IRR); >>%import(PEF_REG); >>%import(EVS_NOT); >>%import(BEF_REG); >>%import(GCS_REG); >>%import(WGS_REG); >>%import(WGS_IRR); >>%import(SMS_PRE); >>%import(SMS_EAR); >>%import(EVS_EAR); >>%import(GCS_EAR);