Hi SAS-Lrs,

   Thought you might be interested.

   I have been searching for ways to insure that EXCEL data is imported correctly. I discovered this method while experimenting with SQL passthru. This is new methodology for me so beware.

/*
Import this CSV into EXCEL
Pat,Age,Sex,Wgt
12,Mary,Female,189345
67,44,Male,1E+12
Roger,103.67,Male,133.3456
103,88,Female,189
*/

/* add and execute this EXCEL VB macro for sheet1 */
/* Just open the visula basic editor then paste and run */
/* this will add a tick mark in front of all the numbers in the sheet */
/* you could add the tick marks manually (quote char) */
/* this forces excel to display the numbers as entered eliminating dangerous
automatic EXCEL formatting */

Sub AddTicks()
    Dim LastPlace, Z As Variant, X As Variant
    Sheets("Sheet1").Select     'Chg for your sheet name
    LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
    ActiveSheet.Range(Cells(1, 1), LastPlace).Select
    Z = Selection.Address   'Get the address
        For Each X In ActiveSheet.Range(Z)  'Do while
            If Len(X) > 0 Then      'Find cells with something
                X.FormulaR1C1 = Chr(39) & X.Text    '39 is code for tick
            Else
                X.FormulaR1C1 = ""  'If empty do not put tick
            End If
        Next
End Sub

/* next highlight the rectangle that has the imported data and name the rectangle QUO */

/* Unlike the libname engine you can leave the EXCEL file open */

/* how many columns have at least one character cell */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");
    select * from connection to Excel
        (
         Select
             count(*) + sum(isnumeric(pat)) as numchrpat,
             count(*) + sum(isnumeric(sex)) as numchrsex,
             count(*) + sum(isnumeric(age)) as numchrage,
             count(*) + sum(isnumeric(wgt)) as numchrwgt
         from
              quo
        );
    disconnect from Excel;
Quit;

/*
Output

The SAS System

numchrpat  numchrsex  numchrage  numchrwgt

        1          4          1          0

As you can see Column Pat has one char field, Sex is all character,
Age has one character and Wgt is all numeric
*/

/* lets get the max lengths for each column */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");

    select * from connection to Excel
        (
         Select
              max(len(pat)) as lenpat,
              max(len(sex)) as lensex,
              max(len(age)) as lenage,
              max(len(wgt)) as lenwgt
         from
              quo
        );
    disconnect from Excel;
Quit;

/*
The SAS System

     lenpat       lensex       lenage       lenwgt

          5            6            6            8
*/

/* take special care with the numeric field so we do not lose precision */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");

    create
         table quo as
    select
         pat length=5 format=$5.,
         sex length=6 format=$6.,
         age length=6 format=$6.,
         input(wgt,E20.8) as wgt format=e15.8

    from connection to Excel
        (
         Select
              pat,
              sex,
              age,
              format(wgt,"##0.00000000E+00") as wgt
         from
              quo
        );
    disconnect from Excel;
Quit;

proc print data=quo;
run;

/*
The SAS System

Obs    pat      sex       age                   wgt

 1     12       Female    Mary       1.89345000E+05
 2     67       Male      44         1.00000000E+12
 3     Roger    Male      103.67     1.33345600E+02
 4     103      Female    88         1.89000000E+02
*/


Some othe fun things you can do with pass thru

proc sql dquote=ansi;

  connect to excel (Path="h:\oto\tables.xls");

    create table cnv as
    select * from connection to Excel
        (
         Select
           Switch(Sex="Male","Table",sex="Female","Desk") AS Expr1,
           str(now()) as curtym,
           IIF(Wgt>150, "Yes", "No") as onoff,
           mid(sex,1,1) as chr1,
           format(wgt,"####.00000000") as tst,
           wgt
         from
           demog
        );

    disconnect from Excel;

Quit;


proc sql dquote=ansi;

  connect to excel (Path="h:\xls\tables.xls");

    execute(
      update `h:\xls\Tables`.demog demog
      set age=888,
          wgt=789,
          sub=age+wgt
      where pat=12
    ) by excel;

    disconnect from Excel;

Quit;

proc sql dquote=ansi;

  connect to excel (Path="h:\oto\tables.xls");

    execute(insert into tst
     values( 12345, 33, 'Female', 120,'Today')) by excel;

    disconnect from Excel;

Quit;

libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
    update tbl.demog
    set age=199,
        sex='none'
    where pat=12;
quit;
libname tbl clear;

libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
    update tbl.play
    set wgt=(select sum(wgt) from tbl.play where pat<88888)
    where pat=99999;
quit;
libname tbl clear;

/* declare columns as character */
proc sql dquote=ansi;

  connect to excel (Path="h:\xls\tables.xls");

    execute(
      update `h:\xls\Tables`.jyn jyn
      set pat='                  ',
          age='                  ',
          treat='                  ',
          lab=  '                  ',
          val=  '                  '
    ) by excel;

    disconnect from Excel;

Quit;

libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
  create
         table trn as
  select
         put(l.pat,5.) as pat,
         put(l.age,5.) as age,
         c.treat as treat length=16 format=$16.,
         r.lab as lab length=3 format=$3.,
         put(r.val,5.2) as val
  from
         tbl.demog as l left join tbl.treatment as c on l.pat=c.pat
         left join tbl.lab as r on l.pat=r.pat
  order
         by pat;
quit;
proc contents data=tbl.jyn position;
run;
data tbl.jyn;
   n=_n_;
   modify tbl.jyn;
   set trn;
run;
libname tbl clear;


/*
Import this CSV into EXCEL
Pat,Age,Sex,Wgt
12,Mary,Female,189345
67,44,Male,1E+12
Roger,103.67,Male,133.3456
103,88,Female,189
*/

/* add and execute this macro for sheet1 */
/* this will add a tick mark in front of all the numbers in the sheet */
/* you could add the tick marks manually (quote char) */
/* this forces excel to display the numbers as entered eliminating dangerous
automatic EXCEL formatting */

Sub AddTicks()
    Dim LastPlace, Z As Variant, X As Variant
    Sheets("Sheet1").Select     'Chg for your sheet name
    LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
    ActiveSheet.Range(Cells(1, 1), LastPlace).Select
    Z = Selection.Address   'Get the address
        For Each X In ActiveSheet.Range(Z)  'Do while
            If Len(X) > 0 Then      'Find cells with something
                X.FormulaR1C1 = Chr(39) & X.Text    '39 is code for tick
            Else
                X.FormulaR1C1 = ""  'If empty do not put tick
            End If
        Next
End Sub

/* next highlight the rectangle that has the imported data and name the retangle QUO */

/* Unlike the libname engine you can leave the EXCEL file open */
/* how many columns have at least one character cell */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");
    select * from connection to Excel
        (
         Select
             count(*) + sum(isnumeric(pat)) as numchrpat,
             count(*) + sum(isnumeric(sex)) as numchrsex,
             count(*) + sum(isnumeric(age)) as numchrage,
             count(*) + sum(isnumeric(wgt)) as numchrwgt
         from
              quo
        );
    disconnect from Excel;
Quit;

/*
Output

The SAS System

numchrpat  numchrsex  numchrage  numchrwgt
ャャャャャャャャャャャャャャャャャャャャャ
        1          4          1          0

As you can see Column Pat has on Char field, Sex is all character,
Age has one character and Wgt is all numeric
*/

/* lets get the max lengths for each column */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");

    select * from connection to Excel
        (
         Select
              max(len(pat)) as lenpat,
              max(len(sex)) as lensex,
              max(len(age)) as lenage,
              max(len(wgt)) as lenwgt
         from
              quo
        );
    disconnect from Excel;
Quit;

/*
The SAS System

     lenpat       lensex       lenage       lenwgt
ャャャャャャャャャャャャャャャャャャャャャャャャャ
          5            6            6            8
*/

/* take special care with the numeric field so we do not lose precision */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");

    create
         table quo as
    select
         pat length=5 format=$5.,
         sex length=6 format=$6.,
         age length=6 format=$6.,
         input(wgt,E20.8) as wgt format=e15.8

    from connection to Excel
        (
         Select
              pat,
              sex,
              age,
              format(wgt,"##0.00000000E+00") as wgt
         from
              quo
        );
    disconnect from Excel;
Quit;

proc print data=quo;
run;

/*
The SAS System

Obs    pat      sex       age                   wgt

 1     12       Female    Mary       1.89345000E+05
 2     67       Male      44         1.00000000E+12
 3     Roger    Male      103.67     1.33345600E+02
 4     103      Female    88         1.89000000E+02
*/



/* passthru to excel */

libname tbl excel "h:\xls\tables.xls";
proc contents data=tbl._all_ position;
run;
libname tbl clear;

/*
#    Variable     Type    Len

1    Pat          Num       8
2    Age          Num       8
3    Narrative    Char      6
4    Wgt          Num       8
5    Sub          Char      1

Format(Number,"General Number")
Format(Number,"Currency")
Format(Number,"Fixed")
Format(Number,"Standard")
Format(Number,"Percent")
Format(Number,"Scientific")
Format(Number,"Yes/No")
Format(Number,"True/False")
Format(Number,"On/Off")
Format(Number,"$#,##0.00%)

 Mask can include combinations of $,#,0,% - is Internationally Aware
*/

proc sql dquote=ansi;

  connect to excel (Path="h:\oto\tables.xls");

    create table cnv as
    select * from connection to Excel
        (
         Select
           Switch(Sex="Male","Table",sex="Female","Desk") AS Expr1,
           str(now()) as curtym,
           IIF(Wgt>150, "Yes", "No") as onoff,
           mid(sex,1,1) as chr1,
           format(wgt,"####.00000000") as tst,
           wgt
         from
           demog
        );

    disconnect from Excel;

Quit;

proc sql dquote=ansi;

  connect to excel (Path="h:\xls\tables.xls");

    create table aris as
    select * from connection to Excel
        (
         Select
           pat,
           narrative
         from
           arisg
        );

    disconnect from Excel;

Quit;


proc contents data=aris;
run;
proc print data=cnv;
format _all_;
run;

/* how many columns have at least one character cell */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");
    select * from connection to Excel
        (
         Select
             count(*) + sum(isnumeric(pat)) as numchrpat,
             count(*) + sum(isnumeric(sex)) as numchrsex,
             count(*) + sum(isnumeric(age)) as numchrage,
             count(*) + sum(isnumeric(wgt)) as numchrwgt
         from
              quo
        );
    disconnect from Excel;
Quit;

/* get the lengths of column data*/
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");

    select * from connection to Excel
        (
         select
             pat,
             sex,
             age,
             wgt
         from
         (Select
              "`"+pat as pat,
              "`"+sex as sex,
              "`"+age as age,
              "`"+wgt as wgt
          from
              quo)
        );
    disconnect from Excel;
Quit;
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");

    select * from connection to Excel
        (
         Select
              isnumeric(""+pat) as numpat,
              isnumeric(""+sex) as numsex,
              isnumeric(""+age) as numage,
              isnumeric(""+wgt) as numwgt
         from
              pat
        );
    disconnect from Excel;
Quit;


/* get lengths  */
proc sql dquote=ansi;
  connect to excel (Path="h:\oto\tables.xls");
    select * from connection to Excel
        (
         Select
             pat,
             len(""+pat) as tst,
             sex as lensex,
             age as lenage,
             wgt as lenwgt
         from
              pat
        );
    disconnect from Excel;
Quit;

proc sql dquote=ansi;

  connect to excel (Path="h:\oto\tables.xls");

    select * from connection to Excel
        (
         Select
           count(*) as reccnt,
           sum(isnumeric(pat)) as numnum,
           len(age) as lenage
         from
              pat
        );

    disconnect from Excel;

Quit;

proc sql dquote=ansi;

  connect to excel (Path="h:\xls\tables.xls");

    execute(
      update `h:\xls\Tables`.demog demog
      set age=888,
          wgt=789,
          sub=age+wgt
      where pat=12
    ) by excel;

    disconnect from Excel;

Quit;

proc sql dquote=ansi;

  connect to excel (Path="h:\oto\tables.xls");

    execute(insert into tst
     values( 12345, 33, 'Female', 120,'Today')) by excel;

    disconnect from Excel;

Quit;

libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
    update tbl.demog
    set age=199,
        sex='none'
    where pat=12;
quit;
libname tbl clear;

libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
    update tbl.play
    set wgt=(select sum(wgt) from tbl.play where pat<88888)
    where pat=99999;
quit;
libname tbl clear;

/* declare columns as character */
proc sql dquote=ansi;

  connect to excel (Path="h:\xls\tables.xls");

    execute(
      update `h:\xls\Tables`.jyn jyn
      set pat='                  ',
          age='                  ',
          treat='                  ',
          lab=  '                  ',
          val=  '                  '
    ) by excel;

    disconnect from Excel;

Quit;

libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
  create
         table trn as
  select
         put(l.pat,5.) as pat,
         put(l.age,5.) as age,
         c.treat as treat length=16 format=$16.,
         r.lab as lab length=3 format=$3.,
         put(r.val,5.2) as val
  from
         tbl.demog as l left join tbl.treatment as c on l.pat=c.pat
         left join tbl.lab as r on l.pat=r.pat
  order
         by pat;
quit;
proc contents data=tbl.jyn position;
run;
data tbl.jyn;
   n=_n_;
   modify tbl.jyn;
   set trn;
run;
libname tbl clear;

/* does not work */
libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc contents data=tbl.sheet2;
run;
proc datasets library=tbl;
modify sheet2;
 rename f1=pat;
quit;
proc contents data=tbl.sheet2;
run;
libname tbl clear;