topic Access unix sas dataset By vba in SAS Enterprise Guide

Access unix sas dataset By vba

deleted_user — Fri, 09 Oct 2009 00:18:14 GMT

Hi,

Any one know how the vba code is to access Unix SAS datasets?
Thanks.

Cathy

Re: Access unix sas dataset By vba

sbb — Fri, 09 Oct 2009 00:55:04 GMT

Search the SAS support http://support.sas.com/ website either using the site's SEARCH facility or using a Google advanced search adding the site:sas.com parameter to limit the search to the SAS.COM site.

There are technical references and conference papers on the topic.

Scott Barry
SBBWorks, Inc.

Re: Access unix sas dataset By vba

DavidD — Fri, 09 Oct 2009 13:09:13 GMT

Cathy: I find your question interesting. In what context are you using VBA with SAS. Are you modifying SAS programs with it? David

Re: Access unix sas dataset By vba

deleted_user — Tue, 13 Oct 2009 00:39:34 GMT

Yes.We can read PC SAS dataset from vba side make excel or powerpoint report automation
(vba code:
Set Conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
Conn.Provider = "sas.localprovider.1"
).
And I am tring read unix sas dataset. Any suggestino?

Thanks.

Cathy

Re: Access unix sas dataset By vba

deleted_user — Tue, 13 Oct 2009 00:42:16 GMT

I tried google this question. Got nothing. I might not key in correct keywords.
Do u get something back?
Thanks.
Cathy

Re: Access unix sas dataset By vba

Patrick — Tue, 13 Oct 2009 11:21:18 GMT

You will need the SAS ODBC driver installed.

The following links might point you into the right direction:
http://www.sas.com/offices/europe/uk/newsletter/feature/3jun_jul03/ht1.html
http://support.sas.com/techsup/technote/ts626.html

HTH
Patrick

Re: Access unix sas dataset By vba

deleted_user — Wed, 14 Oct 2009 00:08:42 GMT

Partick
Thank you very mcuh. I believe ODBC is the best way. and I am trying using the guide (You told me the website)
.
I still have couple questions about ODBC installation in unix side
1) My administore told me might special lisence for ODBC installation (Need buy), Is that true?
2)On the remote unix platform, does have to keep connecing to PC all the time
My thoughs is Local pc connect to unix whenever needed. such as Oracle &Access ODBC.
Thanks again.

Cathy

Re: Access unix sas dataset By vba

ChrisHemedinger — Wed, 14 Oct 2009 15:46:13 GMT

Here are some of your options, and they vary based on what SAS software you have:

- use the SAS ODBC driver to connect to your Unix server running SAS/SHARE, which must be configured to allow access to the data in SHARE libraries.

- use the SAS OLE DB IOM Data Provider to connect to your Unix server running SAS Integration Technologies. You must have SAS IT on the Unix machine, configured with an object spawner (same as you would need to connect with SAS Enterprise Guide).

- download the Unix data from the server machine as sas7bdat files and try to read with the SAS OLE DB Local Data Provider. Looks like you tried that and it didn't work, likely because the encoding of a sas7bdat file is different among different hosts (Windows vs. Unix) and the local data provider cannot read the Unix-encoded data set. You might be able to convert/output the data set from Unix in an alternate encoding for this purpose.

EG 4.2 includes a Data->Download task that lets you bring this data to your PC from a UNIX host. Your SAS for Windows (if you have it) can read this data, but the local provider might not be able to.

Chris

Re: Access unix sas dataset By vba

deleted_user — Wed, 14 Oct 2009 17:03:14 GMT

Chris
Thank you so much. I think ODBC and OLE will be good solutions. I wil try these two at same time.
Currently, I have PC SAS in my computer. When I use PC SAS I can easliy use OLE (pc sas provider) by VBA ADO automated read SAS dataset. Because high cost for the lisence, We will use Unix SAS (And will have enterprise installed) instead of PC SAS. Using object spawner will be the good way. I will try that though I don't have idea how to setup. If you can give me more suggstion on spawner will be great.
Thanks again.

Cathy

Re: Access unix sas dataset By vba

Patrick — Thu, 15 Oct 2009 12:32:35 GMT

Hi Cathy

This information shows you what components are needed for ODBC:
Introducing the SAS ODBC Driver: What Software Do I Need?
http://support.sas.com/onlinedoc/913/getDoc/en/odbcdref.hlp/swreqs.htm

There is a own manual for ODBC available in the SAS OnlineDoc (http://support.sas.com/onlinedoc/913/docMainpage.jsp)

For your future installation with SAS Enterprise Guide as client and the SAS Server under UNIX: SAS Enterprise Guide is a .net application connecting to SAS under UNIX -> all components will be there for what you want to do.

By the way: Do you know about SAS for Microsoft Office (AMO) - if not Google it.

Re: Access unix sas dataset By vba

deleted_user — Thu, 15 Oct 2009 16:27:21 GMT

Hi, Patrick
Thank you for help. I am a bad researcher. :-). I will try.
For SAS for microsoft office. As I know, now there are some new added-in software for SAS and MS ,Are you talking about that? I have my added in-SAS software installed. I can access Unix SAS by added-in-menu.
I am programming a regular monthly report, an automated process by vaba, call SAS code to create sas dataset and then output to PPT and excel. I don't want use added-in menu (It is more like one time work) , that is the reason I am trying to find out this vba code.

Thanks.

Cathy

Re: Access unix sas dataset By vba

Cynthia_sas — Thu, 15 Oct 2009 17:34:40 GMT

Hi:
If you have the SAS Enterprise Intelligence Platform (also known as the BI Platform) and you have the SAS Add-in for Microsoft Office, then one of the methods to "automate" the analytic tasks and wizards is to develop SAS Stored Processes. And, in your case, if the stored process was coded to create a dataset and then create a report, you could run the stored process in either Excel or PPT and the results would automatically populate a workbook or a presentation.

There are 2 ways to develop stored processes -- one is to take an EG process flow and create the stored process from a whole process flow, (or you could create a stored process from just one task in a project); alternately, you could export EG code and then selectively create your stored process by manipulating the code. Most folks, however, use the first method -- they create their stored process from an EG project. Then when they deploy the stored process, any user with the appropriate access can run the stored process.

If you build the stored process with parameters, then you can go a step further and have Person #1 run the stored process in Excel and get the report run for Region #1 and you could have Person #2 run the same stored process in PPT and get the report run for the same region or for different region(s).

If you don't know about SAS stored processes, then I highly recommend these papers:
http://www2.sas.com/proceedings/sugi30/135-30.pdf (talks about creating an SP)
http://www.scsug.org/SCSUGProceedings/2005/Davidson_SAS%20ETL%20Studio%20-%20108.pdf (shows the same stored process running in several client applications)
http://analytics.ncsu.edu/sesug/2008/CS-054.pdf
http://www.lexjansen.com/pharmasug/2009/hw/hw06.pdf

cynthia

Re: Access unix sas dataset By vba

deleted_user — Thu, 15 Oct 2009 19:07:39 GMT

cynthia
Thank you.
SAS Stored process is very good.
Do you know how to call stored process by vba? My conern is , since the report I am going create is regular and will be more than 300 reports at same time (By different Category), per reports will include around 30 slides different.
Thanks.
Cathy

Re: Access unix sas dataset By vba

Patrick — Thu, 15 Oct 2009 22:05:15 GMT

Hi Cathy

Why exactly do you have to use vba at all?

Could you describe a bit what you want to achieve in the end?

Cheers, Patrick

Re: Access unix sas dataset By vba

deleted_user — Fri, 16 Oct 2009 15:41:16 GMT

Hi, Patrick
This project's goal is create Monthly powerpoint format report which including around 300 ppt files 30sllide/file, these 300 files format are same, only data different by kind of category. (My plan is using one template file create all different files) And data will be from Unix SAS dataset. Actually I don't mind with which language. however, since clients are familiar ppt and excel these kind of MS. They want MS format's report. Any suggestion?
Thanks.
Cathy

Re: Access unix sas dataset By vba

Patrick — Sun, 18 Oct 2009 11:11:24 GMT

Hi Cathy

If I understand that right then it's about 30 different ppt slides - all the rest is changing data/selections.

I haven't too much experience with SAS AMO but it sounds too me as if this could be a way to go.

Can't you just design these 30 slides together with stored procedures on the SAS side? The stored procedures could then query SAS views – and the SAS views are created in batch and point to the current monthly data. There is an option in SAS AMO to refresh the results whenever someone open the ppt slides – so it’s the user pulling the data and it will always be the most current ones.

The right approach will depend a bit on how the reports will be used and distributed. If there are also some revision requirements then it's often better to produce pdf's.
Listen carefully what your clients really need. If these are business users then business requirements need often some modification and discussion before they become reasonable requirements for an IT design – and worst are business requirements already formulating how IT has technically to fulfill these requirements....

In case your client got the SAS Portal technology then it would be very worthwhile to give this a thought as well.

I found this paper http://www2.sas.com/proceedings/sugi30/006-30.pdf (How to get SAS® data into PowerPoint™ using SAS9) which might give you some more ideas - and I'm sure there is more on the web if you research a bit.

You could also ask people in the "Integration with Microsoft® Office" forum about some real life experiences and recommendations

HTH
Patrick

Re: Access unix sas dataset By vba

deleted_user — Sun, 18 Oct 2009 23:42:20 GMT

Hi, Patrick
Thank you that still take care my question.
My problem is is that the ppt reviewers will not right to access sas datasets. So I cann't set every ppt has stored process is a good way. I am still thinking ODBC, your suggestion earlier, is a very good way, and (am waiting for my SAS administrator finish unix server side setup. He never did that so in test.). I did some projects that access PC sas datasets by proverders . I think will be the same way in unix sas dataset ,however still cann't find out how to create the link between pc MS and unix SAS.
I will post this question to "Integration with Microsoft® Office" forum at same time.
Thanks.

Cathy

Re: Access unix sas dataset By vba

Patrick — Mon, 19 Oct 2009 05:02:34 GMT

Hi Cathy

The design question will be whether you use a pull or push approach.

If it's push then you would have to produce the ppt slides in a UNIX SAS batch session and distribute them to some file system where your customer can access it.

If you choose a pull approach (which I would recommend if the output has to be ppt) then your customers must be able to access the data in one way or the other (true for ODBC or SAS AMO). But the user to access this data could be one single "system" user for all your customers - and this system user has only read access to the data. Using AMO you could set up the appropriate rights in the SAS Metadata.

Regards, Patrick

Re: Access unix sas dataset By vba

deleted_user — Mon, 19 Oct 2009 05:48:48 GMT

Hi, Patrick
Thank you very much. This email give me a good idea.
I thought push-data this WAY too much. U r right , I can use pull data this way (stored process), Just looping by vba : opening ppt, set up stored process automaticlly run, and save this ppt ,repeat until all reports created. So just need sign one authorized person to create these 300 reports. Only I need to do is setup parameter in stored process,make sure different ppt get different data
and make stored process triggle on (when open ppt, run stored process. kind of this function, I am not sure if this function is available). So I need to focus will be stored process.
Thanks a lot.

Cathy

Re: Access unix sas dataset By vba

Patrick — Mon, 19 Oct 2009 21:36:09 GMT

Hi Cathy

Refreshing the data "on0pen" or "onUserRequest" is already part of SAS AMO.

A possible issue with your approach is that you need human intervention to refresh and distribute the monthly ppt slides.

I personally would rather go for pdf output and create and distribute everything using SAS batch on the Unix server (job triggered by a scheduler).
This approach requests no human intervention and is much more controllable.

The other approach would be to distribute the ppt slides already to the end-users - and then these end-users can update their part of the slides (there might be some licensing questions with such an approach).

To have the ppt refresh and distribution centralised so that a real person has to perform regular monthly tasks is not only a bit old fashioned but causes also regular repeating costs - not something a business likes.

I actually see only one advantage in the "pull ppt" approach: The customer can customise the ppt slides later on according to their needs (but also mess them up...).

HTH
Patrick