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 (August 2006, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 31 Aug 2006 05:07:04 -0600
Reply-To:     Alan Churchill <SASL001@SAVIAN.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Alan Churchill <SASL001@SAVIAN.NET>
Subject:      Re: XLAs with DDE to Excel
Comments: To: Steve_Dun <steve.bates003@GMAIL.COM>
In-Reply-To:  <1156999598.923042.163170@e3g2000cwe.googlegroups.com>
Content-Type: text/plain; charset="iso-8859-1"

Steve,

Which area are you referring to? If you are going down the path that makes the most sense IMO (i.e. using Excel to work with Excel), then yes, you can read in user-defined formats. SAS treats formatted values as strings to the outside world which is the only downside.

Complete, step by step guides to working with SAS using .NET is on my website. There are also various guides and papers on working with SAS, Excel, and .NET.

A SAS dataset is an OleDB-compliant datasource which is fully accessible in Excel and to many other programming languages.

Alan

Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Steve_Dun Sent: Wednesday, August 30, 2006 10:47 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: XLAs with DDE to Excel

Just before I get into this new area ie away from DDE can I please get confirmation that it allows SAS datasets with potentially a lot of user defined formats to be written to Excel? Also if anyone has a working example if they could flick me the code to look over and suss out how it all works? Either here or via e-mail. warm regards Steve

Joe Whitehurst wrote: > Kevin is right on target. I merely copied some dated SAS online > documentation. While I would never think of giving Jim Goodnight any > business advice, I don't mind at all giving him some free > psychological advice since I think I know more about this field than > he does. Jim, get a team together to salvage the tremendous > investment you have made in SAS Component Language and kick their > asses into gear to make up for the time and money lost chasing the > Java/DotNet chimera. > > Joe. > > . > > On 8/30/06, Kevin Myers <KMyers1@clearwire.net> wrote: > > Joe provided a nice example. However, this example is written using > > SCL's somewhat outdated CALL SEND syntax. To develop a more > > up-to-date application, you should really use the cleaner, and more > > recently introduced "dot" notation whenever possible, in place of CALL SEND. > > > > s/KAM > > > > > > ----- Original Message ----- > > From: "Joe Whitehurst" <joewhitehurst@gmail.com> > > To: "Eric Hoogenboom" <erichoogenboom@yahoo.com> > > Cc: <SAS-L@LISTSERV.UGA.EDU> > > Sent: Tuesday, August 29, 2006 04:14 > > Subject: *****SPAM***** Re: XLAs with DDE to Excel > > > > > > > Eric, > > > > > > I would rather teach you how to fish than give you fish. But I do > > > appreciate how someone could construe themselves as lazy, or at > > > least try to inveigle me into believing they construe themselves as lazy. > > > For you I will actually give you an example from the documentation > > > I recommended to everyone tired of working with Antiquated tools. > > > > > > > > > Joe > > > > > > SCL Code for Populating a Microsoft Excel Spreadsheet > > > > > > > > > Load an instance of the OLE Automation class and invoke Excel. Set > > > the object to Visible so you can see the automation in progress. > > > LAUNCHXL: > > > > > > hostcl = loadclass('sashelp.fsp.hauto'); call send(hostcl, > > > '_NEW_', excelobj, 0, 'Excel.Application'); call send(excelobj, > > > '_SET_PROPERTY_', 'Visible', 'True'); return; > > > > > > > > > Get the identifier for the current Workbooks property and add a > > > worksheet. Then get the identifier for the new worksheet. CREATEWS: > > > call send(excelobj, '_GET_PROPERTY_', 'Workbooks', wbsobj); call > > > send(wbsobj, '_DO_', 'Add' ); call send(excelobj, > > > '_GET_PROPERTY_', 'ActiveSheet', wsobj); > > > > > > Open a SAS data set. dsid=open('sasuser.class','i'); > > > > > > call set(dsid); > > > rc=fetch(dsid); > > > nvar=attrn(dsid, 'NVARS'); > > > nobs=attrn(dsid, 'NOBS'); > > > > > > > > > Traverse the data set and populate the cells of the Excel > > > worksheet with its data, row by row. do > > > col=1 to nvar; > > > call send(wsobj, '_COMPUTE_', 'Cells',1,col,retcell); > > > var=varname(dsid,col); > > > call send(retcell,'_SET_PROPERTY_', 'Value',var); end; do while > > > (rc ne -1); > > > do row = 1 to nobs; > > > do col = 1 to nvar; > > > r=row+1; > > > call send (wsobj, '_COMPUTE_', 'Cells', r ,col,retcell); > > > if vartype(dsid,col) eq 'N' then > > > var=getvarn(dsid,col); > > > else var=getvarc(dsid,col); > > > call send(retcell, '_SET_PROPERTY_', 'Value' ,var); > > > end; > > > rc=fetch(dsid); > > > end; > > > end; > > > dsid=close(dsid); > > > return; > > > > > > Close the worksheet and end the Excel session. The _TERM_ method > > > deletes the OLE automation instance. QUITXL: > > > call send(excelobj,'_GET_PROPERTY_', 'ActiveWorkbook', awbobj); > > > call send(awbobj, '_DO_', 'Close', 'False'); call send(excelobj, > > > '_DO_', 'Quit'); call send(excelobj, '_TERM_'); return; > > > > > > > > > > > > > > > > > > As you can see from this example, automating an application object > > > requires some knowledge of the object's properties and methods. To > > > help you decide which SCL commands to use for an Excel automation > > > object, you can use the Macro Recorder in Excel to perform the > > > task you want to automate, then look at the Visual Basic code that > > > is generated. It is then relatively simple to map the Visual Basic > > > code to comparable SCL statements and functions. > > > > > > Visual Basic Code Samples and Their SCL Equivalents shows some > > > excerpts of Visual Basic code and their SCL equivalents. > > > > > > Visual Basic Code Samples and Their SCL Equivalents Visual Basic > > > Code OLE Automation in SCL Launch Excel and make it visible > > > > > > Set excelobj = CreateObject("Excel.Application") excelobj.Visible > > > = True > > > > > > hostcl = loadclass('sashelp.fsp.hauto'); > > > > > > call send ( hostcl, '_NEW_', excelobj, 0, > > > 'Excel.Application'); > > > call send (excelobj,'_SET_PROPERTY_', > > > 'Visible','True'); > > > > > > Create a new worksheet > > > > > > Dim wbsobj, wsobj As Object > > > Set wbsobj = excelobj.Workbooks > > > wbsobj.Add > > > Set wsobj = excelobj.ActiveSheet > > > > > > call send(excelobj,'_GET_PROPERTY_', > > > 'Workbooks', wbsobj); > > > call send(wbsobj, '_DO_', 'Add'); call > > > send(excelobj,'_GET_PROPERTY_', > > > 'ActiveSheet', wsobj ); > > > > > > Set the value of a cell > > > > > > wsobj.Cells(row + 1, col).Value > > > =var > > > > > > r=row+1; > > > call send(wsobj,'_COMPUTE_', 'Cells', r, col, > > > retcell); > > > call send(retcell,'_SET_PROPERTY_', > > > 'Value' ,var); > > > > > > Close the Excel application object > > > > > > excelobj.ActiveWorkbook.Close > > > ("False") > > > excelobj.Quit > > > > > > call send(excelobj,'_GET_PROPERTY_', 'ActiveWorkbook', awbobj); > > > call send(awbobj, '_DO_', 'Close', 'False'); call > > > send(excelobj,'_DO_', 'Quit'); call send(excelobj,'_TERM_'); > > > > > > > > > > > > > > > > > > > > > Chapter Contents > > > Previous > > > Next > > > Top of Page > > > > > > > > > Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved. > > > > > > > > > On 8/29/06, Eric Hoogenboom <erichoogenboom@yahoo.com> wrote: > > > > Joe, > > > > > > > > A very lazy data-analyst would like to see a very short OLE > > > > example of > > how > > > > to dump a dataset into an Excel file. That is the OLE equivalent of: > > > > > > > > proc export data = sashelp.class > > > > outfile = "d:\class.xls" > > > > dbms = EXCEL2000 > > > > replace; > > > > run; > > > > > > > > And maybe even make the top row bold. > > > > > > > > Thanks a lot, > > > > Eric > > > > > > > > > > > > > -- > > > To paraphrase G. Santayana, the SAS Macro Facility used by common > > > SAS programmers is an old mate that gives no pleasure and many > > > headaches, yet she/he cannot live without it, and resents any > > > aspersions that strangers may cast on its character > > > > > > > > -- > To paraphrase G. Santayana, the SAS Macro Facility used by common SAS > programmers is an old mate that gives no pleasure and many headaches, > yet she/he cannot live without it, and resents any aspersions that > strangers may cast on its character


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