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 (February 2008, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 28 Feb 2008 07:33:42 -0800
Reply-To:     Peter <crawfordsoftware@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter <crawfordsoftware@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: excel to SAS
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On 28 Feb, 14:11, Nathaniel.Wood...@DOM.COM (Nat Wooding) wrote: > Alan > > Thanks for the clarification. The ones that you list are largely well > outside of my comfort zone so I seldom think of them, even if I have heard > of them. > > How deep is the snow pack out your way? Are you using the second floor door > to leave the house? (vbg) > > Nat > > Nat Wooding > Environmental Specialist III > Dominion, Environmental Biology > 4111 Castlewood Rd > Richmond, VA 23234 > Phone:804-271-5313, Fax: 804-271-2977 > > "Alan Churchill" > <savian001@gmail. > com> To > <Nathaniel.Wood...@DOM.COM>, > 02/28/2008 09:08 <SA...@LISTSERV.UGA.EDU> > AM cc > > Subject > RE: excel to SAS > > A small correction Nat. > > " if you don't have the access to pc files, then you have to fall back to > the older approaches." > > There are a number of other ways of getting Excel data into SAS outside of > older approaches. ODBC, OleDb, XML, Aspose, etc. I can think of a lot of > ways. Some good, some bad, all of them with caveats either due to Excel > limitations, SAS limitations, or comfort zone of the coder. > > Alan > > Alan Churchill > Savianwww.savian.ne > > > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On Behalf Of Nat > > Wooding > Sent: Thursday, February 28, 2008 6:56 AM > To: SA...@LISTSERV.UGA.EDU > Subject: Re: excel to SAS > > Al > > Here is some sample code that I keep on hand. You will find a macro which > reads is used to read the sheets and you will need to modify the input > statement to suit your needs. After this maco is defined, there is a chunk > of code that reads the Excel workbook directory and builds a set of macro > calls which then open and read each sheet, one after another. If you have a > bunch of sheets, this can take a little while. In my case, I read a years > worth of workbooks, each with a dozen or so one-page sheets, and this can > take 10 or 15 minutes since each sheet has to be opened. It is much faster > to read the sheets using the excel libname approach but if you don't have > the access to pc files, then you have to fall back to the older approaches. > > As a final touch, you will hear a series of rising beeps when the job ends. > Since each sheet is opened on your desktop, you pc will essentially be tied > up during the time that SAS is reading the sheets. Hence the beeps. It > tells me to check my computer. > > Good luck > > *************************************; > *** note the data step with the name holddata which follows the %GLOBAL > STMT BELOW. In place of my vars > col1, etc, stick in your variables; > > DM 'LOG; CLEAR'; > DM 'OUTPUT; CLEAR'; > DM 'PGM'; > DM 'CAPS ON'; > DM 'NUM ON'; > TITLE ' '; > > libname infile 'c:\park';** this is the directory holding our file; > > OPTIONS NOXWAIT NOXSYNC MPRINT macrogen symbolgen; > * the options noxwait and noxsync are important when running DDE. > The others are used > * in order to see the macros that are generated; > > %GLOBAL sname file; > > Data HoldData; ** this is a one record file with blank data and it will be > the base to which we append > data as they are read. ; > INFORMAT COL1 - COL3 $30. ; > retain COL1 - COL3 ' '; > run; > > %MACRO DOIT(sname); > * this macro holds the code for reading individual spreadsheets. It is > invoked later by the > * macro runnit ; > > /* THE DDE LINK IS ESTABLISHED WITH EXCEL. LAUNCH EXCEL AND LOAD THE > DESIRED SPREADSHEET */ > > DATA _NULL_ ; > x "'C:\Program Files\Microsoft Office\Office11\excel.exe' > ""c:\park\&FILE..XLs"""; > > RUN; > > DATA _NULL_; > X=SLEEP(5); * PAUSE SAS PROCESSING FOR 5 SECONDS WHILE EXCEL OPENS > AND LOADS THE SHEET; > > DATA READSHEET ; > > FILENAME GETDATA DDE "EXCEL|&sname!R1C1:R3C3"; > > * the code RnCn:RnCn refers to the range of rows and columns that > you want to read. The first > * pair is the upper left corner and the second pair is the lower > right corner. ; > > INFILE GETDATA DLM='09'X NOTAB DSD MISSOVER lrecl=300; > * NOTE THE DLM. THIS SEPaRATES EXCEL FIELDS; > > INPUT @; > _infile_=compress(_infile_,'20'x);* I have a couple sheets that have > hex 20 codes embedded in > * some cells and these mess up the > input statement. Here, > * remove the hex20 before reading > the data; > > INFORMAT COL1 - COL3 $30.; * in my case, I read these columns > * and then go through an elaborate parsing > procedure which I have removed. > * you would want to supply names and formats of > the variables that you would > * want to read; > INPUT @1 COL1-COL3 ; > run; > > DATA _NULL_;** SHUT DOWN EXCEL; > FILENAME CMDS DDE 'EXCEL|SYSTEM'; > FILE CMDS; > > PUT '[QUIT()]'; > RUN; > > proc print; > > proc append base=holddata data=readsheet force; > ** CONCATENTATE THE DATA; > run; > > %MEND DOIT; > **************************************************************************** > > ****; > %macro runnit(file); > %* the following code segment came from koen vyverman and is used to read > the list > of worksheet names from a workbook. it selects valid sheet names and > writes these to > a external file. after these are written to the file, they are included > back in and > executed. This is not the best use of macro code but my macro knowledge > is too rusty > and I do not have the time to write anything more elegant at the moment > *; > > %* Launch the Excel application if necessary ... *; > options noxsync noxwait xmin; > filename sas2xl dde 'excel|system'; > data _null_; > length fid rc start stop time 8; > fid=fopen('sas2xl','s'); > if (fid le 0) then do; > rc=system('start excel'); > start=datetime(); > stop=start+10; > do while (fid le 0); > fid=fopen('sas2xl','s'); > time=datetime(); > if (time ge stop) then fid=1; > end; > end; > rc=fclose(fid); > run; > > %* Open the workbook. *; > data _null_; > length ddecmd $ 500; > file sas2xl; > ddecmd='[open("'||"c:\park"||'\'||"&file"||'")]'; > * all of my workbooks are held in a single folder so I hard code > that and add to this > * name the name of the specific workbook that I want to open > --ie, the macro variable > * file will be the workbook to open; > put ddecmd; > run; > > filename topics dde 'excel|system!topics' lrecl=5000; * this defines an > internal excel table > * that contains the information on the sheets in the > workbook; > > data sheets; * now we read this file and parse out the names of the > sheets; > length topics $1000; > infile topics pad dsd notab dlm='09'x lrecl=300;* lrecl may need to be > larger for some > * applications; > input topics $ @@;* this gives a list of worksheets and other files; > if topics=:'[';* get rid of some of the other files. now, we need to > pick out the > valid sheet names. these will all include the > workbook name; > check=upcase(substr(topics,2,7)); > testname= upcase(substr("&file",1,7)); > if check=:testname;* compare the workbook name with the file name; > length sname $ 10; > sname=scan(topics,2,']');* find the name of the worksheet; > > keep sname; > * to better understand what is happening here, turn off the > * keep statement, and add a proc print. Also, turn off the if check=: ; > run; > > data _null_;* now we build a set of macro calls that will be automatically > used later; > set; > filename storemac 'c:\park\docode.sas'; * write our macro calls to this > file; > > file storemac; > > string=compbl('%doit('||sname||');'); * here we build a macro > invocation for each work sheet > * and write this to a text file. > It will be included > * and used by the macro runnit; > put string; > run; > > %* Close the workbook without saving, kill Excel. *; > data _null_; > file sas2xl; > put '[error(false)]'; * this and the other items enclosed in brackets > are excel version 4 macros > * which happen to still work very nicely; > put '[file.close(false)]'; > put '[quit()]'; > run; > > %include 'c:\park\docode.sas'; * bring in the generated macro calls; > run; > %mend runnit; > **************************************************************************** > > *****; > run; > > LIBNAME infile 'c:\park' ; > > run; > ** at this point, enter the months that you want to run; > > %runnIT(Sample);* manually add as many of these as you have workbooks; > > DATA _NULL_; > > FILE PRINT; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > PUT 'ALL DONE'; > > call sound( 523,160); > call sound(1000,120); > call sound( 523,160); > RUN; > > ***********************************; > > Nat Wooding > Environmental Specialist III > Dominion, Environmental Biology > 4111 Castlewood Rd > Richmond, VA 23234 > Phone:804-271-5313, Fax: 804-271-2977 > > On Feb 27, 11:07 am, Nathaniel.Wood...@DOM.COM (Nat Wooding) wrote: > > Al > > > Following on what Curtis says below, let me know if you need to use DDE. > I > > have sample code that will read the directory of a workbook and generate > > the dde code to read each sheet. > > > Nat Wooding > > Environmental Specialist III > > Dominion, Environmental Biology > > 4111 Castlewood Rd > > Richmond, VA 23234 > > Phone:804-271-5313, Fax: 804-271-2977 > > > "Smith, Curtis, > > Mr, DCAA" > > <Curtis.Smith@DCA > To > > A.MIL> SA...@LISTSERV.UGA.EDU > > Sent by: "SAS(r) > cc > > Discussion" > > <SA...@LISTSERV.U > Subject > > GA.EDU> Re: excel to SAS > > > 02/27/2008 11:54 > > ... > > read more - Hide quoted text - > > - Show quoted text -

Nat

try SAS/Access to ODBC to see the old way to read excel workbook data. I'm sure you would find it within the "comfort zone". Even before SAS9, the ODBC libname engine would treat the sheets and range names in an excel workbook like sas datasets in a "bound library". SAS/Access to OLEdb seems like a more modern and more effective version of SAS/Access to ODBC. Of course the caveat with these SAS/Access libname engines and excel data, is that the quality of the "tables" seen by SAS very much depends on the quality of the data they contain (consider mixed type data). The advantage comes with the number of libname and SAS dataset engine options, (which greatly exceed what PROC IMPORT provides).

Good Luck PeterC

PeterC


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