Date:         Tue, 20 Apr 2004 10:55:29 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Both character and numeric, Problem
Comments: To: "Elmaache, Hamani" <Hamani.Elmaache@CCRA-ADRC.GC.CA>
Content-Type: text/plain

Hamani:
I'll have to guess what the error messages mean. One of the perverse
features of MS Excel allows users (often unwittingly) to enter data of
different data types (character, numeric, date, etc.) in the same column.
That could happen naturally, for example, where columns contain missing
values. SAS and all RDBMS's require, for good reason, that all values in one
column have the same data type.

We have found that after using Excel to convert the Excel spreadsheet to a
*.csv file (one of the 'SAVE AS' options), SAS can read the *.csv file
correctly. Otherwise you'll have to set the data type of columns to one data
type per column.

Although widely used for data entry, Excel spreadsheets merely shift the
burden of constructing good electronic data entry forms from the PC
developer to the database programmer. One has to understand that when
clients tell you that they are saving you time and resources by using
spreadsheets to enter the data for you. It doesn't work that way.
Sig

-----Original Message-----
From: Elmaache, Hamani [mailto:Hamani.Elmaache@CCRA-ADRC.GC.CA]
Sent: Tuesday, April 20, 2004 9:40 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Both character and numeric, Problem


Hi there.
I'm reading data from Excel using this code:

libname xl odbc
 noprompt='dsn=Excel Files;
 dbq=D:\ctb\ctb1999_p.xls';

proc sql noprint;
 select distinct "xl.'"||trim(memname)||"'n"
        into :xlsheets separated by ' '
  from dictionary.members
  where libname='XL' and index(memname,'$')>0;
  QUIT;

data all;
 set &xlsheets;
run;

My problem is I get message like this:

1254   set &xlsheets;
ERROR: Variable DOB4 has been defined as both character and numeric.
ERROR: Variable COUNTRY4 has been defined as both character and numeric.
ERROR: Variable DOB5 has been defined as both character and numeric.
ERROR: Variable COUNTRY5 has been defined as both character and numeric.


and I could't get SAS data.
Can somebody help?
Thaks a lot.
Hamani.