STP stream to Excel - report increases data size

[FredGIII]

Hi everyone,

I have created a stored process to pull a data set from a database that I want to stream to Excel.  I want the user to be able to launch the STP from Excel and auto populate the data into an Excel tab.  When I run the program on EG the resulting data set is approx 11MB exported to a CSV file.  But when I create a report, the size jumps to over 120MB!!!!  My understanding is that I have to create the report in order to stream it to Excel.  Is that correct?  Is there a more efficient means to do this so that I can stream it to Excel?  There are 127K rows which is well within the limits for Excel, but the report size is over 10x the csv file size.  Any suggestions?

Thanks,

Fred

[jakarman]

Use AMO  ..... do an export to  XLSX.

Copying plain text will cause a lot of overhead. The tagsets ODS for example is an uncompressed xml file. As Excel will zip you have your difference in sizing.

[FredGIII]

Jaap,

I am using AMO.  My understanding though was that for the STP to automatically stream the data into Excel, you had to have it output as a report. If I do an export to XLSX, isn't that going to be stored in a file location and then the user would have to run the STP, then have to go retrieve the file and open?  Certainly, possible but I would prefer to have to go directly into Excel.  Without exploding the file size!

Fred

[jakarman]

Ok Fred, You did not mention what you are using and what you have done.
Using a STP to create a report could be the issue. A report is not the same as a spreadsheet. When it is storing in printable layout there could be a lot of overhead.

With a STP you can also create SAS (or other) datasets and have them imported to Excel in a dataset way. That is more the way of exporting data to Excel with Eguide.

That interface has no row limit count for Excel as it can scroll into SAS as kind of views.  

[FredGIII]

Hi Jaap,

Sorry, I should have mentioned the use of AMO.  You are right about the report containing a lot of overhead, but based on what I have read so far, I can't just have an STP create a dataset if I want it automatically streamed to Excel. Is that incorrect? 

You said

"With a STP you can also create SAS (or other) datasets and have them imported to Excel in a dataset way. That is more the way of exporting data to Excel with Eguide."

I am not clear on what you mean here.  Are you saying just have the STP create the dataset and then from the SAS AMO tab in Excel, go to the SAS Data folder and browse to the library where the dataset is stored?  Of course that means it can't be stored in the Work library. And it does require the user to launch the STP and then know to go to the appropriate library to find the file.  Or is there an automated way to accomplish that?

Thanks,

Fred

[Quentin]

I don't know AMO well.

Agree with the general issue, xml (and html) can be very verbose.  Such is life.  Often when I think I have a slow stored process, what I really have is a fast stored process but a big html file that takes a long time to return to user.  So we have to think about optimizing in different ways.

I imagine using  Stored Process Web App, you could have it stream a csv file back to the browser, and if you set the appropriate html headers the user would get a prompt to open it in Excel or save it.  CSV of course not have all the fancy formatting you can do with tagsets.excelxp or whatever, but the benefit is the much smaller file size and file download. And you have to trust Excel to read in the csv appropriately.

I don't know if the AMO can "consume" csv results sent to it.  It probably wants sasreport xml or something like that.  But you could try it. 

Sometimes you can get smaller file sizes by changing the ods style to one that does minimal formatting (style=minimal or similar)  Taking a look at the xml might give you a sense of how much might be saved by this approach.

--Q.

[Cynthia_sas]

Hi:

  Inside AMO, you have the choice for how to return stored process results. If you create your report with a lot of extra "cosmetics" using colors and fonts, then the report output will be large. But in AMO, you can also "pull" data directly into Excel. See the screen shot of the AMO ribbon and the difference between pulling data (using the SAS Data button) and running a report (using the Reports button). And if you are going to use the Reports button or run a stored process, you have the choice of how results are returned to you (see pull down for Options in the screen shot) -- you can choose SAS Report format output, HTML format output or CSV format output from your stored process.

cynthia

[FredGIII]

Hi Cynthia,

Thanks for the information. All my questions revolve around running Stored Processes from Excel through the Reports button.

1. Pull data using SAS Data button -
   1. If I run an STP, I can have it create a dataset, but a couple of issues - one the user then needs to understand that they then need to use the Data button to browse to the appropriate Library and pull the data.  The data can't be stored in a temporary Work library because the Work library that the stored process stores data in will not be the same as the one accessed by the end user. So now I have worry about cleaning out a library periodically.  (correct me if I am wrong )
2. STP - through Reports
   1. Changing the options in Excel - SAS tab so that results format are CSV, but that means the end user needs to understand the STP they are using and if it is just data or if it is data plus graphs and remembering to switch the option to CSV and back again.  Is there a way to automate that from the server side?  I really would prefer that the Excel user not have to modify options or worry about what type of STP they are running etc. 

So bottom line, yes it works if I change the options in Excel. But as a broader approach, this isn't ideal. I would like non-SAS users who have MS add-in to be able to run any of the STP's without having to have pre-knowledge about how the data is returned to them. But, from what I can tell, this isn't possible.  I am hoping I am wrong.

Regards,

Fred

[Quentin · Accepted solution]

Cynthia is the expert, but I'll jump in on #2 just for fun.

I'm happily surprised to see the add in is happy to consume a CSV.

Given that the result format options are HTML/SAS Report/CSV, one would assume that when the user chooses this, they get passed to the stored process as &_odsdest and similar macro vars.

So if I were you (again, I don't know AMO much), I would select the CSV option, run the stored process from addin, then look at the SAS log to see what it did.  With %Put _all_ ; at the top of your stored process code, etc.

Hopefully it will be reasonably clear.  And hopefully in your code for your stored process you could just hard code %let _odsdest=csv; etc , before you call %stpbegin().  So this would over-ride the value selected by the user.

Haven't done it, but that's the approach I would take.

[FredGIII]

Quentin,

Thank you!  That does work.  The key, as it turns out, is that you cannot put this code into the Source code within EG. If you do, then when you create the STP, then the %let is enclosed within the %stpbegin() and does not work. So you are correct when you say hard code.  Basically, you have to go edit the code using the SAS Management Console (SMC) and add

%global _odsdest;

%let _odsdest=csv;

before the %stpbegin().

This way the end user launching the STP from Excel (MOA) doesn't need to switch his settings or know what type of STP they are running. Whew!

Thanks again Quentin.

Fred

[Quentin]

Glad it worked!

Yes, EG really really likes to add %stpbegin and %stpend around your source code.  There is a check box that you can uncheck so that it won't, but seems like every time I edit a stored process in EG it tries again to add them.  SMC makes it easier to control the source code (i.e. generates less wrapper stuff for you), which you need in this case (and many cases... : )

--Q.

[jakarman]

There are your dilemma's. Using the report interface is a good fixed delivery easily to be refreshed.  The report overhead layout to be accepted.

Wanting the data into excel  for further processing (Why) opens up an other way. Processing the data first and deliver that into a dataset to be accessed. The full analytics menus in A MO are having a lot in common with Eguide.

Defining a dataset available for a user or user or even sas work are possible options.

[haikuobian]

It is true that in many occasions, for end users, the less they need to do/know, the better. If just for the purpose of importing data into Excel using AMO, what I did is at the end of STP,  do a Proc Print on your target table. So when end users run the STP via AMO, the data will be loaded into their Excel. Most of my users like it that way, as it is very similar to opening up an spreadsheet on their own.

my 2 cents,

Haikuo

[FredGIII]

Haikuo,

I did try just doing a Proc Print (along with various options to minimize formatting) but found that the Proc Print still dramatically increased the file size. That may be ok if the data is relatively small, but typically this results data set is in the 100's of thousands of rows and CSV files size of ~16MB.  So if you start increasing the file size dramatically with formatting information, it quickly becomes unmanageable.  I found that Proc Print and it's ilk increased file sizes > 10X.   If you know of a way to do a Proc Print that will not increase the file size, I would love to know.  That would make life easier.

Regards,

Fred