SAS BI tips, lessons learned, problems and successes

Questions (or search strings) lead people to my blog or my email box on a daily basis and many of these are related to what SAS Stored Processes can or cannot do.

• Can a SAS Stored Process query an RDBMS?
• Can a SAS Stored Process create a data table?
• Could it create an OLAP cube?
• Can a SAS Stored Process create a PDF file (tip: use _ODSDEST), how about an Excel file?

Thankfully all I have to say is 'YES!', if a SAS program can do it then so can a SAS Stored Process (and with little to no tweaking). That is what makes SAS Stored Processes so incredibly awesome, the SAS developer can switch roles - from a bottleneck 'Run this job for me now' person to an enabler, creating reusable content that anyone can use immediately and with a couple minutes of click-this training.

Just remember, because all .sas files COULD become SAS Stored Processes doesn't mean that they all SHOULD become SAS Stored Processes. For example, .sas files that need to be run automatically and/or require no user interactions are not necessarily candidates for a SAS Stored Process. But .sas programs that require some modification to run for a specific need and/or are run when requested are absolutely good candidates. Can you think of other .sas programs that should (or should not) be considered for the SAS Stored Process job?

For more examples of what SAS Stored Process programs look like and can do, check out The 50 Keys to Learning SAS Stored Processes book. There are 35 SAS Stored Process examples covered and key after key of developing them.

Stored process code can exist within the metadata itself for version 9.3 (the benefits of which I discussed earlier). But for all other versions (and as a option in version 9.3) the SAS code is  stored as a .sas file within the server's file system (or mapped folder/drive structure). When editing stored process code directly, you need to know where the stored process .sas file resides.

Here are a few places to find that .sas file location. Do you have any other ways?
1. Through the SAS Management Console properties screen, select the Execution tab.

2. Using PROC STP (SAS 9.3)

 options metaserver = 'machine'
 metaport = 8561
 metauser = 'XXXXXXXXXX'
 metapass = XXXXXXXXXX;
 PROC STP PROGRAM='/Projects/Book Examples/12_d_AMOParetoEdits';
 list group=execution;
 run;

The output then provides:
-------------------------
NOTE: PROC_STP: ====== Stored Process: /Projects/Book Examples/12_d_AMOParetoEdits ======
NOTE: PROC_STP: ====== Metadata Listing for /Projects/Book Examples/12_d_AMOParetoEdits ======
NOTE: Logical Server: SASApp - Logical Stored Process Server
NOTE: Source Code Repository: C:\SAS\Stp Book
NOTE: Source File: Example_12d_AddInParetoEdits.sas
NOTE: Result Type: Packages No
NOTE: Streaming Yes
NOTE: PROC_STP: ====== End Metadata Listing for /Projects/Book Examples/12_d_AMOParetoEdits ======
NOTE: PROC_STP: ====== Stored Process: /Projects/Book Examples/12_d_AMOParetoEdits Return Status
= 0 ======
NOTE: PROCEDURE STP used (Total process time):
real time 0.63 seconds
cpu time 0.04 seconds

3. By running a metadata call
In the following example, I have a sas program that will pull out a full list of all the stored processes registered in the metadata, each corresponding .sas file and where in the server these physical files reside.

/*insert an options statement here to connect to your metadata server/port.*/
data new(keep=dirname stpname stpcode);
label dirname="Directory Name";
label stpname="Stored Process Name";
label stpcode=".sas File Name";
 length uri name dirname stpuri stpcode stpassn stpname $256;
  uri="";
  name="";
  dirname="";
  stpuri="";
  stpcode="";
  stpassn="";
  stpname="";

n=1; nobj=1;
    do while(nobj >= 0); 
        nobj=metadata_getnobj("omsobj:Directory?@Id contains '.'",n,uri);
    put nobj;
        if (nobj < 0) then leave; 
        rc=metadata_getattr(uri,"Name",Name); 
    rc=metadata_getattr(uri,"DirectoryName",DirName);
    if Name = "SP Source Directory" then do;
       rc1=1; x=1; 
        do while(rc1>0);
      rc1=metadata_getnasn(uri, "Files", x, stpuri); 
      rc2=metadata_getattr(stpuri, "FileName", stpcode); 
      rc3=metadata_getnasn(stpuri, "AssociatedTransformation", 1, stpassn);
      rc4=metadata_getattr(stpassn, "Name", stpname); 
      x+1;
       output;
      end;
    end;
        n+1; 
    end; 
run;
title 'All Stored Process Locations';
proc print label noobs;
run;

Included below is a screenshot of the resulting report.

What if you would like to make a copy of an existing stored process to test out your changes before making them live? The copy command is available from within  SAS Management Console or from the SAS Enterprise Guide Open Stored Process GUI as seen below (note you can only paste the object in a different folder). The server designation, the prompts, etc are copied. And this is great if you would like to change and test only a metadata modification (such as switching from 'Stored Process' to 'Workspace' servers) or adding descriptions to prompts. But what about the underlying .sas code if you want to edit the stored process itself?

(Need help with editing? Check out my co-author Tricia Aanderud's post about how to edit a stored process? )

Remember that in the majority of stored process implementations, there are two components to every stored process:

1. the metadata registration
2. the .sas code that runs on submission

(New in 9.3, you have the option to place sas code in the metadata registration itself, see my post about when this would be super useful.)

Anyhow, the copied metadata registration points to the original .sas code not a new .sas file. Modifying the stored process from Enterprise Guide immediately will cause you to edit BOTH the original stored process code and the new copy. (If you move to the Summary tab of a Stored Processes Properties, you can confirm that the source code path is identical to the original stored process.) You must make a copy of the .sas file in a subsequent step and edit the copied metadata to point to this second location before beginning any edits through the SAS Code window.

Included are the steps to do this full copy:

1. Copy the stored process metadata registration and paste it in a separate folder
2. Edit the stored process metadata registration to point to .sas copy
   

   Move to the Execution Options area of the Stored Process Properties to change the .sas file name.

3. Copy the .sas file on the server itself, rename to match #2.
   

   Ensure that this name exactly matches the properties screen.

   This is a great tip to remember. Maybe Tricia & I should consider it in a second edition of the SAS Stored Process book. Just goes to show you how difficult it was to come up with only 50 Keys to Learning SAS Stored Processes.