When we think about using SAS with Excel, one begins to groan at the myriad of options - DDE, SAS Addin, various Excel-ish library engines, IOM in VBA, etc etc etc.

What if there were a simple technique, quick to set up, and easy to adjust?

A technique that works on ANY version of Excel, requires no install, works cross platform and encompasses full metadata security (at point of connection)?

A technique that has been available to us for at least 15 years¹?

For those of us who work with SAS Web Applications, the idea of streaming data via a URL is not unusual.  It’s a great approach as it ensures we can get the latest version of data, using the latest version of the code, with the permission set granted to that user at that point in time.

Extending this concept to Excel is as easy as 1,2,3:

1 - Set up the Stored Process

The code below should be registered as a stored process using SAS Management Console with STREAMing output:

/* STP URL Parameters */ %global type libds; /**  * assign metadata library from libref  */ %let libref=%scan(&libds,1,.); data _null_;   length lib_uri LibName $200;   if metadata_getnobj("omsobj:SASLibrary?@Libref='&libref'",1,lib_uri) then do;     rc=metadata_getattr(lib_uri,"Name",LibName);     call symputx('libname',libname);   end; run; libname &libref meta library="&libname";  /* set content type if CSV */

data _null_;

  if "%upcase(&type)"="CSV" then

    rc=stpsrv_header("Content-type"

      ,"Content-Type: text/csv; encoding=utf-8"

    );

run;

 /* send data */ PROC EXPORT DATA=&libds OUTFILE=_webout DBMS=&type REPLACE; run;

 

2 - Set up the Workbook

The 'trick' here is simply to set up a web query to call the STP above.  The options vary as per the version of Excel, here is the path for Excel 2010: Next step is to add the (appropriately formatted) URL, eg below.  Note that I called my STP "SAAS" and placed it in a metadata root folder called Web.

https://YOURSASMID:8080/SASStoredProcess/do?_PROGRAM=/Web/SAAS&libds=sashelp.class&type=tab

We are asking for the sashelp.class dataset, and for it to be TAB delimited.  This gets pasted into the web query box, and gives results as follows: We now have our data in excel, but for those who wish to avoid the need to 'right click / refresh' we should:

3 - Automate

The beauty of this approach is that it is so easy to automate in VBA!

By the Power of Greyskull

Now that our STP is configured, it is gobsmackingly straightforward to get the power of SAS into Power BI.  Again, simply select Get Data and Web: Enter an appropriately formatted URL, using type=CSV in the parameters:

https://YOURSASMID:8080/SASStoredProcess/do?_PROGRAM=/Web/SAAS&libds=sashelp.class&type=CSV

You may then need to select the appropriate credentials with which to connect (eg windows authentication for IWA, or use Basic if you normally enter passwords for SAS).

In the final screen simply mark the type of your input:

and - voila!   Of course this is a very simple example, but using the above template you can start to add more parameters on the SAS side - enabling where clauses, drop statements, business logic etc.

The same approach can be used to get data into Powershell, Python, R, uncountable client applications, and of course - javascript (see building web apps with SAS).

Got any other tips?  Please share in the comments below..

References: ¹ Vincent DelGobbo discusses web queries with SAS/IntrNet in this SUGI27 (2002) paper  2 Stig Eide also discusses this approach in this blog post from 2013