Date: Mon, 5 Dec 2005 20:57:07 -0700
Reply-To: Alan Churchill <SASL001@SAVIAN.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Alan Churchill <SASL001@SAVIAN.NET>
Subject: Re: need help to read a sasdata set and copy that in excel
In-Reply-To: <BAY101-F391C47AAF28C2B5F76B5FFDE400@phx.gbl>
Content-Type: text/plain; charset="us-ascii"
Toby,
First, Excel graphs suck but people know how to use them. Office 12 will do
a much better job but we are over a year away.
In a good systems architecture, you should have delineation between layers.
This allows for encapsulation of problems, easier maintenance, and the
ability to change out the layers as the need arises. It isn't perfect, but
it's a good concept.
Presentation Layer
Business Logic Layer
Data Layer
If you wrap all of this together as 1 unit, it is hard to debug, maintain,
and replace layers. SAS typically fits well as the Business Logic layer but
oftentimes handles all 3. We all have used SAS for all 3. However, I don't
think it is proper to always view it that way especially for production
systems.
Imagine this:
Presentation Layer Excel
Business Logic Layer SAS
Data Layer SQL Server
Now this is more realistic for many of the problems that arise in the field.
Now here is the language choices that are best at the level:
Presentation Layer VBA, VB.NET, C#
Business Logic Layer SAS, Macro
Data Layer SQL
Why are these language choices best: because they are designed for that
system. Ask the DB gurus on SAS-L whether they use Base SAS for their DBMS
systems or do pass-thru SQL. Chances are the latter because it works better.
Just as it works downstream, it also works upstream for the GUI languages.
I would challenge you to come up with a SAS solution to a complex (or easy)
Excel problem and see if it would compete against an Excel .NET solution.
Use whatever technology you deem (DDE, Proc Export, ExcelXP tagset, etc.). I
will assure you that using Excel libraries is faster, easier to code, easier
to maintain, and easier to understand.
Make it a bet on a dinner at SUGI. Restaurant of choice would be Fog City
Diner. You can choose the criteria for the tests as long as the SAS-L
denizens approve ;-]
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 toby
dunn
Sent: Monday, December 05, 2005 8:31 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: need help to read a sasdata set and copy that in excel
Alan,
Excel does do a better job at creating some graphs, however why go to the
trouble of exporting or importing data when SAS can create the graph in the
first place, if it can't do it then sure go forth and use Excel. But if SAs
can do just as good a job then it seems silly to me, extra work comes to
mind, or even say hard to maintain.
The point I was making is that one should not make their life any harder
than it already is by adding Vb .Net, c++, Java or whatever language you
feel like substituting in here <> to the mix, so long as SAS can get the job
done and that is your starting point. In this case I read it as they were
starting in SAS and then trying to get VB code to extract data data values
from a SAS data set. The best solution if one had to do the graphas in
Excel is simply by createing VB macros in a workbook, then having SAS push
the data to it, then have SAS execute the Macros. i believe there was a
SUGI paper over this thingy.
Now if there was a need for something that Excel, Java, .net, c++, etc...
could do better, easier, or was the only way that it can be done then by all
means go do it in that language. but don't do it unless you have too or are
bored. besides there are so many other extremely interesting problems that
we as programmers havent even solved yet.
Toby Dunn
From: "Alan Churchill" <SASL001@savian.net>
To: "'toby dunn'" <tobydunn@HOTMAIL.COM>,<SAS-L@LISTSERV.UGA.EDU>
Subject: RE: need help to read a sasdata set and copy that in excel
Date: Mon, 5 Dec 2005 16:05:55 -0700
Toby,
I have to disagree on both points.
How does it help having SAS do the graphs if users need graphs in Excel? SAS
would merely generate an image that would then be static.
Several programming languages in a solution is quite common and is done all
the time on SAS-L. SCL, Macro, SQL, and Base are all used here and all are
different languages. Many also use scripts and perl. Adding in VB or C#
should not cause consternation especially considering the power you gain.
SAS cannot replicate the Excel libraries. The XP tagset can certainly do a
lot but it cannot compete against the libraries for speed and power. That
applies to either reading or writing sheets.
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 toby
dunn
Sent: Monday, December 05, 2005 2:52 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: need help to read a sasdata set and copy that in excel
Suresh,
The easiest way I can see to do this is to have SAS Push your data into your
excel spead sheet. Have some VB macros there that will get your graphs.
Or better yes simply have SAS do your graphs for you. There by taking
excel out of the picture all together (my preference), the few programs and
laguages you have to use the better.
Toby Dunn
From: "suresh babu" <suresh160@rediffmail.com>
Reply-To: "suresh babu" <suresh160@rediffmail.com>
To: "toby dunn" <tobydunn@HOTMAIL.COM>
CC: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Re: need help to read a sasdata set and copy that in excel
Date: 5 Dec 2005 21:42:16 -0000
i am trying to build a automised model monitoring tool using vba and sas.
i have some code that has to be run in a sequence which generates freq and
means of different data sets.
i have done all the above portion.
now what i have to do is, i have to get the data from the proc means tables
into the main excel sheet and then form some graphs out of that.
For this process, i know a way,i.e proc export into temporary excel sheet
and then copy that to my main sheet. but this is some kind of long
procedure.
what i am trying to do is,
i am trying to read the data set and put that in a record set in VBA and
then copy that into my main work book. i wrote the below code to do that
particular task but i wasnt successful.
any advices are most welcome in correcting it.
thanks alot.
On Tue, 06 Dec 2005 toby dunn wrote :
>suresh,
>
>Perhaps you could tell us what you are wanting to do, in the big picture
>sort of sense, rather than trying to get us to help you with your VB
code.
>
>Just from what I saw you are going about the whole excerise from the
wrong
>approach. i think you would be better served having SAS push out an
excel
>file rather than having Excel read a SAS data set and grab values. But
>since I am guessing at what you want I could be wrong.
>
>
>
>Toby Dunn
>
>
>
>
>
> From: suresh babu <suresh160@REDIFFMAIL.COM>
>Reply-To: suresh babu <suresh160@rediffmail.com>
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: need help to read a sasdata set and copy that in excel
>Date: Mon, 5 Dec 2005 18:43:18 -0000
>
>hi all,
>i am trying to read a dataset on my c driver through excel(coding in VBA)
>and get the values pastedin excel.
>i am able to invoke the sas and read the data set but unable to capture
the
>values and paste them in excel.
>
>here is the code
>
>Dim obConnection As ADODB.Connection
>Dim obRecordset As ADODB.Recordset
>Dim i As Integer
>
>Set obConnection = New ADODB.Connection
>Set obRecordset = New ADODB.Recordset
>
>obConnection.Provider = "sas.LocalProvider.1"
>obConnection.Properties("DataSource")
>"C:\starmodel\starmodellibrary\overall_stab.sas7bdat"
>
>obConnection.Open
>
>obRecordset.Open
>
>
>fldcount = obRecordset.Fields.Count
>Set colnames = obRecordset.Fields
>
>ReDim flds(0 To fldcount - 1)
>
> For ColNum = 0 To fldcount - 1
> Set flds(ColNum) = obRecordset.Fields(ColNum)
> Next
>
>' Place column headings on sheet
> ThisWorkbook.Sheets("sheet2").Range("A1").Select
>
> For icols = 0 To fldcount - 1
> ThisWorkbook.Sheets("sheet2").Cells(1, icols + 1).Value =
>colnames(icols).Name
> Next
>obRecordset.Close
>Set obRecordset = Nothing
>obConnection.Close
>Set obConnection = Nothing
>
>end sub
>
>
>can anyone currect my code or give some adviceon it.
>
>thanks for ur time
>
>thanks
>suresh
