SAS Stored Process Usage Reporting

[scrapppit]

Hello,

I was wondering if it's possible to view the SAS Stored Processes Usage anywhere. We have a bunch of stored processes and would like to see how many times they are run, the most run report, and who are the power users. Is there any macro that exists?

In SQL Server it's possible to query the execution log and get a bunch of information on reports and users, does the same exist in SAS?

Thank you.

[jimbarbour]

SAS does have a feature like that.  It is called the RTRACE facility. I'm not sure if RTRACE can tell the difference between a Stored Process and just a regular SAS job, but one can definitely track usage by User ID.

First, one has to change the config file to turn on the tracking/logging feature.  It's a fairly simple change to the sasv9_usermods.cfg file:

-RTRACE ALL
-RTRACELOC "/path to your log storage location/PID%p_DATE%d:%t.log"

Be careful if you are in a Windows environment.  The % directive symbols in the RTRACELOC parameter may need to be changed to # in Windows, and the / should be changed to \.

The RTRACE facility will then write a log file to the location specified in RTRACELOC for every SAS batch job or interactive session.

One then has to write a SAS program to parse the log files and make sense of them.  I used the following paper as the basis for a program that I wrote:  https://support.sas.com/resources/papers/proceedings17/0161-2017.pdf

I didn't find the exercise trivial, but I did get it to work.  Let me dig up some output, and I'll post it here.

Jim

[Quentin]

Another option is to create your usage log.

For example, you can make a perm SAS dataset to store stored process name, user ID who called the macro, list of parameters passed, the time called, time completed, etc. 

Then in the stored process code, have it capture that data into a one record dataset and PROC APPEND it to the perm dataset.

With that, you've got nice usage dataset you can use for trending or whatever.  I like collecting the parameters passed, because it lets you see *how* people are using a stored process.  And can clue you in to design changes which would make users' lives easier.  I also store the results of a log scan, so I can know when users get an error.  It's amazing how many people don't complain when they get an error.

I've got a nightly job that runs, analyzes the records that were added to the usage log dataset during the prior day, and emails me a report.

[jimbarbour]

A little more on RTRACE:  RTRACE is going to write out log files.  These log files record every file that is accessed. 

Here's a sample from one of my logs:

File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasext\tkstring.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxkloc.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxdtu.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxbam.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sashelp\core.sas7bcat
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sase7lu.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sase7xrt.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxsite.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sase7l.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sase7xgt.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxzcrp.dll
File closed: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxzcrp.dll
File closed: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxsite.dll
File closed: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxkini.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxsmw.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sastksrv.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasext\tkeavl.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxbtch.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sase7ld.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\saswfdte.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\saswftme.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\uwfeurdf.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasxsrin.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sasyys.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sasexe\sase7yse.dll
File opened: E:\Program Files\SASHome\SASFoundation\9.4\core\sashelp\regstry.sas7bitm

You can determine the UserID by looking at which registry files, etc. are accessed.  Basically, you have to write a program that data mines the log files.  If a given class of data is present, you're only limited in terms of what you can learn by your programming skills.  I was looking for product usage, which is mainly what RTRACE is for.  Here's the report that I developed:

Now, can you determine if a Stored Process was used?   I don't know, but this is the most extensive usage tracking feature I know of in SAS.  If this is not going to give you what you need, you might want to talk to your SAS rep.  There are other logging facilities associated with SAS; I'm just not very familiar with them.  @ChrisHemedinger helped me one time about 5 years ago with Log4Net type logging; perhaps he can help us here.

Jim

[andreas_lds]

---

@scrapppit wrote:

Hello,

I was wondering if it's possible to view the SAS Stored Processes Usage anywhere. We have a bunch of stored processes and would like to see how many times they are run, the most run report, and who are the power users. Is there any macro that exists?

In SQL Server it's possible to query the execution log and get a bunch of information on reports and users, does the same exist in SAS?

Thank you.

---

The logs of the Stored Process Server should contain all the information you want: name of the user, name of the code file and a timestamp, of course. I wrote "should", because it depends on the setup of your environment if the data is actually written to a log-file.

[jimbarbour]

@scrapppit,

I don't know if you've found the answer you need yet, but there is a community just for Stored Processes here in the SAS communities.  Perhaps it's worth a post there?

Link:  https://communities.sas.com/t5/SAS-Stored-Processes/bd-p/sas_stored_processes

Best of luck,

Jim

[NovGetRight]

The logs from SAS STPs are by default under  Config\Lev1\SASApp\StoredProcessServer\Logs,  you can write code to analysis the content, who opened it and run it. There is no exist tools can do this, since the content of the logs are free text.  And one application normally are developed by more than one SAS STPs (SAS code in behind),  in order to summarize it more easily, you can design your code to record and output some info into one database, once the program is running by someone, and maintain it.

For myself, I just standardlize the filename of the SAS program of the STPs, then simply catch the keyword of the logs then do the summary.  And print them out using JavaScript package.