Desktop productivity for business analysts and programmers

Access unix sas dataset By vba

Reply
N/A
Posts: 0

Access unix sas dataset By vba

Hi,

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

Cathy
Super Contributor
Super Contributor
Posts: 3,174

Re: Access unix sas dataset By vba

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.
N/A
Posts: 0

Re: Access unix sas dataset By vba

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

Re: Access unix sas dataset By vba

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

Re: Access unix sas dataset By vba

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
Respected Advisor
Posts: 4,135

Re: Access unix sas dataset By vba

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
N/A
Posts: 0

Re: Access unix sas dataset By vba

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
Community Manager
Posts: 2,889

Re: Access unix sas dataset By vba

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
N/A
Posts: 0

Re: Access unix sas dataset By vba

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
Respected Advisor
Posts: 4,135

Re: Access unix sas dataset By vba

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.
N/A
Posts: 0

Re: Access unix sas dataset By vba

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
SAS Super FREQ
Posts: 8,820

Re: Access unix sas dataset By vba

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
N/A
Posts: 0

Re: Access unix sas dataset By vba

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
Respected Advisor
Posts: 4,135

Re: Access unix sas dataset By vba

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
N/A
Posts: 0

Re: Access unix sas dataset By vba

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
Ask a Question
Discussion stats
  • 21 replies
  • 1253 views
  • 0 likes
  • 6 in conversation