Access unix sas dataset By vba

[Patrick]

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

[deleted_user]

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

[Patrick]

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

[deleted_user]

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

[Patrick]

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

[deleted_user]

Patrick
You are right. pdf is good solution.
One of reason my clients want PPT, is they can see datasheet easily.To avoid mess up data, I always set up file read only /password.

BTW, I just get feed back from SAS adminstrator, he told me no AMO for powerpoint. Is it right?
Thanks for geting lots info from you.

Cathy

[Patrick]

Hi Cathy

There is definitely a SAS AMO for PowerPoint. Have a look at the product docu: http://support.sas.com/documentation/onlinedoc/addin/index.html

I believe SAS AMO is a great way to empower end-users to do their own analysis and create some adhoc-reports without the need of knowing a lot about programming.

For what you want to achieve I think that you risk a lot of pain when you give ppt slides to the end-user for regular reporting. It's less about the data source. I agree that you can protect it by granting read access only.
But what are you going to do about the ppt slides which are under the end-users control? How do you avoid that your end-users start changing the slides until they are no more functional - and then start "screaming" that "the system" is faulty.
I promise you that this is going to happen in one way or the other and that you will have to intervene and fix slides month after month.
Also: Because a lot of end-users won't understand the difference between slides with a "data link" and their own "static" slides you risk a "inflation" of change request. You will get little understanding that some of the requested and so simple looking changes would be a pain to realise.

If your end-users want access to the underlying data to produce their own adhoc-reports: Give them either access via SAS AMO (then best done in Excel) or produce monthly data extracts for them (i.e. csv files).

There is another issue with SAS AMO (and also any VBA solution): All this stuff depends on the MS Office version and it wouldn't be the first time that some functionality doesn't work properly anymore after MS Office got updated.

The "bad" thing about push and pdf: Unless you've got experience with SAS/Graph then creating reasonable graphs with SAS could become quite a challenge and is for sure more difficult than creating the same graphs in MS Office.

HTH
Patrick