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.
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
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.
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
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;
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
@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.
sas eg 4.3
office 2007
sas add-in for microsoft office 4.3
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.