BookmarkSubscribeRSS Feed
Amar
Calcite | Level 5

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.


9 REPLIES 9
Haikuo
Onyx | Level 15

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

Amar
Calcite | Level 5

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.


Haikuo
Onyx | Level 15

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

stat_sas
Ammonite | Level 13

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;

jakarman
Barite | Level 11

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 --<-----
jakarman
Barite | Level 11

@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 --<-----
Amar
Calcite | Level 5

sas eg 4.3

office 2007

sas add-in for microsoft office 4.3

jakarman
Barite | Level 11

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 --<-----
nakpakpan
Calcite | Level 5
 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 3525 views
  • 0 likes
  • 5 in conversation