As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Suppress Output of Modified Registered Tables in AMO

From sasCommunity
Jump to: navigation, search

I develop stored processes used by our reporting department through the Excel AMO. I wanted to keep track of which stored processes were run, by whom, and the options that were selected. Writing the code was no problem, it involved updating a table registered on the metadata server every time the stored process was run. However, I found that by default the Excel AMO outputs each modified registered table as a separate tab when the stored process is run. The end-users really didn't need to see the raw metareporting datasets, so I needed to figure out a way to suppress the output of these tables.

The solution was to update a table that was NOT registered with the metadata server. I could still easily access the contents of the table by creating a view and registering just the view.

For example, suppose my metareporting dataset is called TMP_V. Perhaps it was initialized with the following code:


   data RPTING.TMP_V;
       thisrun = 0;
   run;

This table is not registered with the metadata server. Then create a view table:


   data RPTING.TMP / view = RPTING.TMP;
       set "%sysfunc(pathname(RPTING))/TMP_V";
   run;

This table is registered with the metadata server.

The stored process code which then updates the metareporting dataset would then look something like:


  libname RPT_V "%sysfunc(pathname(RPTING))";
   
  data RPT_V.TMP_V (drop = last_thisrun);
   set RPT_V.TMP_V end=lastobs;
   retain last_thisrun 0;
   output;
   
   last_thisrun = thisrun;
   if lastobs then do;
       thisrun = last_thisrun + 1;
       output;
   end;
   
  run;

Since this stored process does not modify the registered table, no table output is sent to Excel.


--Sterling Paramore 18:23, 25 March 2010 (UTC)