topic Re: sas excel automation in SAS Programming

sas excel automation

Amar — Thu, 24 Apr 2014 13:21:57 GMT

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.

Re: sas excel automation

Haikuo — Thu, 24 Apr 2014 14:20:17 GMT

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

Re: sas excel automation

Amar — Thu, 24 Apr 2014 14:27:05 GMT

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.

Re: sas excel automation

jakarman — Thu, 24 Apr 2014 14:36:15 GMT

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

Re: sas excel automation

Haikuo — Thu, 24 Apr 2014 14:41:39 GMT

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

Re: sas excel automation

stat_sas — Thu, 24 Apr 2014 14:45:10 GMT

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;

Re: sas excel automation

jakarman — Thu, 24 Apr 2014 14:50:16 GMT

@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.

Re: sas excel automation

Amar — Thu, 24 Apr 2014 15:19:54 GMT

sas eg 4.3

office 2007

sas add-in for microsoft office 4.3

Re: sas excel automation

jakarman — Thu, 24 Apr 2014 15:32:43 GMT

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?

Re: sas excel automation

nakpakpan — Fri, 09 Nov 2018 21:53:31 GMT