BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

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

Cathy
21 REPLIES 21
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
I tried google this question. Got nothing. I might not key in correct keywords.
Do u get something back?
Thanks.
Cathy
DavidD
Calcite | Level 5
Cathy: I find your question interesting. In what context are you using VBA with SAS. Are you modifying SAS programs with it? David
deleted_user
Not applicable
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
Patrick
Opal | Level 21
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
deleted_user
Not applicable
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
ChrisHemedinger
Community Manager
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
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
deleted_user
Not applicable
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
Patrick
Opal | Level 21
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.
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Patrick
Opal | Level 21
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
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 2329 views
  • 0 likes
  • 6 in conversation