sas excel automation

Reply
Occasional Contributor
Posts: 13

sas excel automation

hi sas gurus

i am completely new to sas and excel plugin

we currently have a 5 reports using the same olap cube but with different filters. finally when all these 5 reports are run, we create a final excel sheet using data from all these 5 excel sheets.

basically the user wants to automate the process.

any ideas would be appreciated to point me in the right direction.


Respected Advisor
Posts: 3,124

Re: sas excel automation

Not sure what do you mean by automation? If it is 100% automation in my book, then I will run a batch job.

We have similar scenarios that we leverage Stored Process to process Cubes, then generate reports. You will still need EG to script STPs, then Add-in can take from there.

Haikuo

Occasional Contributor
Posts: 13

Re: sas excel automation

hi

thanks for your reply

what i mean is that we use the same cube and set different filters and export different views of that cube into excel.

then we finally create one excel sheet, that is final result.

now i dont know much, but i was thinking about creating vbscripts to automatically connect to sas cube and get results and format it.

is this approach even possible.


Respected Advisor
Posts: 3,124

Re: sas excel automation

I knew little to nothing about MDX, so I won't attempt to use VBscripts to call for it, but if you do, I think it could be a viable approach.

This is what I would do:

Repeating all of the procedures involving the cube (filters, slices, different views, exporting to excel etc.) in EG, then use the EG generated code to make your own Stored Process. You can fire up your Stored Process using SAS Microsoft Add-in from within Excel. Mission accomplished. Fairly straightforward and painless.

I myself used to be a hardcore old school Base programmer, and now I am a slacker, it is all EG's fault. LOL.

Good Luck,

Haikuo

Trusted Advisor
Posts: 1,204

Re: sas excel automation

If you want to export output automatically from SAS to Excel in different sheets then this may be helpful

ODS TAGSETS.EXCELXP

FILE='C:\test.xls'

STYLE=minimal

OPTIONS ( Sheet_Name = 'A' );

PROC PRINT DATA=sashelp.shoes; RUN;

ODS TAGSETS.EXCELXP

OPTIONS ( Sheet_Name = 'B' );

PROC PRINT DATA=sashelp.class; RUN;

ODS TAGSETS.EXCELXP CLOSE;

Valued Guide
Posts: 3,208

Re: sas excel automation

The SAS plugin has a refresh option, by that SP will run and new results will become visible. Somewhat similar as pressing f9 on a field to refresh

Still the question what do you mean by automation 

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: sas excel automation

@Amar, What do you have? please specify. The SAS plugin SAS/Amo?  Which SAS version? Which office version? What kind of server? What type of Olap?

All is SAS but not all of SAS is equal.


---->-- ja karman --<-----
Occasional Contributor
Posts: 13

Re: sas excel automation

sas eg 4.3

office 2007

sas add-in for microsoft office 4.3

Valued Guide
Posts: 3,208

Re: sas excel automation

These versions is a comnbination with probably SAS 9.2 and it will work with Excel Word and PowerPoint but not with Outlook (that is a newer version).

With AMO you can do some SAS Analytics (importing data) but also running SAS-Stored processes.

Doing it that way you do some filtering at the SAS-server side and you have clean/ready to present reports. This all being managed form the central SAS metadata repository.

You can be seduced by importing just data into Excel and doing the processing in Excel.

You can import cubes by the SAS OLE/DB (using mdx) or by those mentioned tagsets approach.

As you mentioned SAS/Amo, I am assuming you are using the SP approach. Is this correct?

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 8 replies
  • 458 views
  • 0 likes
  • 4 in conversation