LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2008, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: sas-l@uga.edu
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;


Back to: Top of message | Previous page | Main SAS-L page