Date:         Thu, 10 Aug 2006 16:16:55 +0200
Reply-To:     Andre Wielki <wielki@INED.FR>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Andre Wielki <wielki@INED.FR>
Subject:      Re: Proc Import Question
Comments: To: "Bonett, John" <JBonett@ETS.ORG>
Comments: cc: toby dunn <tobydunn@HOTMAIL.COM>
In-Reply-To:  <3B1DEF5FA42FBD4A9117ACBD2CABC84D05036AC9@rosnt108.etslan.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

It is more complicate!  (sas 9.1.3 sp4)

-guessingrow is only in use  for csv or dlm source  in proc import

- mixed=yes/no  following the manual who say
"converts numeric data values into character data values for a column that contains
  mixed data types. This option is valid only while importing data from Excel.
The default is NO, which means that numeric data will be imported as missing
values in a character column. If MIXED=YES, then the engine will assign a SAS
character type for the column and convert all numeric data values to character
data values. This option is valid only while reading (importing) data into SAS."

so i tried the full possibilities with your column in excel (standard cell)
 > My data in column A in Excel is as follows:
 > Var1,1,2,3,4,5,6,7,8,~,alpha.

PROC IMPORT OUT= WORK.blah2
DATAFILE= "c:\blah2.xls"
             DBMS=xls REPLACE;
       GETNAMES=YES; MIXED=Yes;
RUN;
proc print data=blah2;run;

PROC IMPORT OUT= WORK.blah3
DATAFILE= "c:\blah2.xls"
             DBMS=xls REPLACE;*sheet='ALL FORMS';
       GETNAMES=YES; MIXED=no;
RUN;
proc print data=blah3;run;
PROC IMPORT OUT= WORK.blah4
DATAFILE= "c:\blah2.xls"
             DBMS=EXCEL REPLACE;
      SHEET="ALL FORMS"; GETNAMES=YES; MIXED=YES; SCANTEXT=YES;
      USEDATE=YES;SCANTIME=YES;
RUN;
proc print data=blah4;run;
PROC IMPORT OUT= WORK.blah5
DATAFILE= "c:\blah2.xls"
             DBMS=EXCEL REPLACE;
      SHEET="ALL FORMS"; GETNAMES=YES; MIXED=no; SCANTEXT=YES;
      USEDATE=YES;SCANTIME=YES;
RUN;
proc print data=blah5;run;

the results are

for blah2 and blah3   char column
Obs    VAR1

   1    1
   2    2
   3    3
   4    4
   5    5
   6    6
   7    7
   8    8
   9    ~
  10    alpha

and for blah4 and blah5  num column

Obs    var1

   1      1
   2      2
   3      3
   4      4
   5      5
   6      6
   7      7
   8      8
   9      .
  10      .

But i have studied before the case of mixed date into a column

some cells with a real excel date right aligned
and some cells with date as text like 12/31/1989...

in the case of import with mixed=yes all the dates are becoming
correct chat text like 21/31/1989 left aligned and
  a format like anydtdte10.  with an input function makes the conversion
   except for a excel date like 01/00/1900  which is transformed into missing .

in the case of an import with mixed=no  then only the numeric exceldate
are converted and the 01/00/1900 date become 30dec1899! the starting point
for an excel date!

HTH
Andre



--
Andre Wielki
INED (Institut National d'Etudes Démographiques)
133 Boulevard Davout 75980 Paris Cedex 20
33 (0) 1 56 06 21 54          FRANCE