Date: Mon, 19 May 2008 09:52:00 -0700
Reply-To: xlr82sas <xlr82sas@AOL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: xlr82sas <xlr82sas@AOL.COM>
Organization: http://groups.google.com
Subject: Re: Determining the type and length of EXCEL columns
Content-Type: text/plain; charset=ISO-8859-1
On May 18, 9:13 pm, xlr82sas <xlr82...@aol.com> wrote:
> 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 visual basic editor in EXCEL 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 in front of all numeric data, manually
> */
> /* 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;
For a cleaner vesion of this post go to
http://homepage.mac.com/magdelina/.Public/utl.html
First bullet point
| 
