Stored Process - Download Data

[itchyeyeballs]

Hi all,

Can anyone point me towards a beginners guide to creating a stored process that would allow users to download large amounts of data to csv or Excel? I've found several articles on the web but they all seem to assume a base level of knowledge.

We have been using the export facility from a table but are running into row count issues.(We have Office Analytics but I'm giving up on getting IT to install it for relevant users)

Basic requirements are:

• What is the basic SAS code to export csv or Excel reliably
• Where would the SAS program be saved on the VA server?
• Are they are gotchas or issues that we should be aware of with using sroed processes for this purpose?

Any help appreciated!

[JuanS_OCS · Accepted solution]

Hi there @itchyeyeballs,

do I understand correctly that you are trying to make work such STPs on a SAS VA server?

Before answering to your questions, if you try to let the users to download the data from a report into a csv or Excel, there is a metadata cabaility that will grant the users to right click on a table and download the data from it.

Now, coming back to your questions:

• What is the basic SAS code to export csv or Excel reliably - Perhaps this code might help you http://support.sas.com/kb/40/683.html 
• Where would the SAS program be saved on the VA server? - A stored process can be stored in the metadata itself or in a .sas file, you can select the location.
• Are they are gotchas or issues that we should be aware of with using sroed processes for this purpose? - probably I am not the best one to answer this question. The one I can think of is that an Excel file will have some limitations set by Microsoft https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269...

Hope it helps.

Kind regards,

Juan

[itchyeyeballs]

Great thank you, yes we are trying to get this to work on SAS server.

We cant use the list table object as theres too much data (100,000 + rows and growing)

That stored process looks perfect, will give it a go (for some reason clicking the hyperlink you posted doesnt work, I had to copy/paste it - maybe my browser)

Much apreciated

[itchyeyeballs]

Just to quickly re-vist this 

We got things working but found a glitch (maybe only affects our set-up) where the stored process would randomly fail.

This turned out to be due to us using a LASR table as the data source, SAS support advised this is supported in theory but not best practice as the data has to be read from memory into the SAS session each time, on thier advice we switched to using a sas7bdat file saved to the server and the issue went away.

We now have to figure out how to replace the LASR row level security with an implimentation in the stored process that works with the sas7bdat file but thats a whole other thread (probably)

[paulo94]

Hi,

could you please send a new link? This one is off.

Tks,

[itchyeyeballs]

Hi, If you mean the sas support link just copy/paste to your browser, will work then