Date: Sun, 18 May 2008 21:13:48 -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: Determining the type and length of EXCEL columns
Content-Type: text/plain; charset=ISO-8859-1
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;
