Chapter 2: Creating SAS Stored Processes for the Finance Department
2.2.1 Tabulating Charges by Dates of Service and Payment
2.2.2 Calculating Paid/Charged Amounts Ratios
2.2.3 Tracking Costs, Revenue, and Enrollment
2.3.1 Creating a Stored Process with Linked Prompts
2.3.2 Chaining Stored Processes
2.4 Modifying a Stored Process
2.5 Creating Stored Processes with Complex Macro Programming
2.5.1 Multiple Values Selection in Prompt
2.5.2 All Possible Values Selection in Prompt
2.1 Introduction
SAS programmers in the IT department at Healthy Living Inc. deliver reports to the analysts in the Finance department. These reports answer the following questions:
1. What are the company’s outstanding liabilities for a given month? At the beginning of each month, analysts in the Finance department rush to close the books on the previous month. This includes estimating payments for claims that have not yet been settled. Formally, these expenses are called incurred but not reported (IBNR) expenses.
2. What is the financial state of the company? The Finance department analyzes trends in costs, revenue, and enrollment on a monthly, quarterly, and annual basis.
3. Why was a specific payment large? The Finance department is responsible for explaining outlier payments.
SAS programmers write voluminous amounts of code to fulfill the Finance department’s analytical requirements. Their code follows a predictable pattern. First, it retrieves data from the company’s data warehouse. Next, the data is massaged and standardized based on the Finance department’s definitions. A reporting procedure, such as PROC REPORT or PROC TABULATE, is applied to the data. Finally, SAS results are exported to an Excel worksheet, which is sent as an e-mail attachment to the Finance department.
A typical SAS report is run periodically or run once, and then discarded.
SAS programmers spend much of their time running periodic reports or writing code to research specific payments. They are not happy about this. Statisticians by training, they believe that they can provide higher value to the company by devoting their time to analytics.
This chapter describes how SAS programmers can convert their code into stored processes. Analysts in the Finance department can run the stored processes at their own convenience using familiar tools such as Excel. Stored processes save SAS programmers time and enables them to work on higher-end analytics.
2.2 Basic Stored Processes
This section creates three stored processes.
1. Tabulating Total Charges by Dates of Service and Payment
2. Calculating Historical Ratio of Paid/Charged Amounts
3. Observing Trends in Revenues, Costs, and Enrollment
The first two stored processes help the Finance department estimate the previous month’s liabilities. Estimation of IBNR expenses is conceptually easy. It can be done in various ways. One way is to calculate outstanding charges, and then multiply these with a historical ratio of paid/charged amounts. The results of the first stored process produce outstanding charges for the previous month. The results of the second stored process yield a paid/charged amounts ratio.
The third stored process creates a graph of trends in revenues, costs, and enrollment. The Finance department uses the graph often in reporting to the CEO and the Board.
The process of estimating IBNR expenses is rife with complicated and competing actuarial models, which is outside the scope of this book. Instead, a simple way of estimating IBNR expenses is provided. Finance departments of health-care companies often use this estimation method as a way to cross-reference results from actuarial models.
2.2.1 Tabulating Charges by Dates of Service and Payment
The SAS BI flagship product, SAS Enterprise Guide, enables users to perform data management and analysis tasks entirely through a point-and-click interface. In this book, the first stored process is built from code developed through the SAS Enterprise Guide interface.
Healthy Living Inc.’s data mart tables are stored in the DW library, which has been pre-assigned by its SAS administrators. The Encounter table is used to create a tabular report, breaking out total charges by dates of payment and service. This view of the total charges is a key piece of data in estimating IBNR expenses.
If a library is pre-assigned, then it is available to you at the start of a SAS Enterprise Guide session. No LIBNAME statement is necessary. Libraries can be created and pre-assigned in SAS Enterprise Guide or SAS Management Console.
1. Open the Encounter table from the DW library in SAS Enterprise Guide. One way to do this is by selecting Open → Data from the File menu.
2. To create a tabular report, select Describe → Summary Tables Wizard from the Tasks menu. This begins the Summary Tables Wizard that generates reports through PROC TABULATE.
3. On page 2 of the Summary Tables Wizard, select AmtCharged and its corresponding Sum statistic as an analysis variable.
4. On page 3 of the Summary Tables Wizard, select YearMonthPaidDt and YearMonthDOS as classification variables.
Figure 2.1: Summary Tables Wizard in SAS Enterprise Guide
5. After all of the steps are completed, convert the information in the Summary Tables Wizard into a stored process. Right-click on the task under Process Flow in the Project Tree window, and select Create Stored Process. This begins the Create New SAS Stored Process wizard.
6. On page 1 of the Create New SAS Stored Process wizard, enter information as shown in Figure 2.2.
a. Enter the name of the stored process in Name.
b. Select a Location. This is where users navigate to access the stored process. BI content is stored in folders created by metadata, not folders created by the physical operating system. SAS folders and its subfolders cannot be viewed in Windows Explorer.
c. Enter a description and keywords. These are used during searches, so it is important to populate these fields with pertinent information.
Figure 2.2: Create New SAS Stored Process Wizard
7. On page 3 of the Create New Stored Process wizard, specify how code is executed and stored.
a. Select SASApp from the Application server drop-down menu. This server runs code generated by users. A server is simply a program that executes other programs.
b. Select Default server under Server type. This allows the client to choose between SASApp Stored Process Server or SASApp Workspace Server.
c. Select Allow execution on selected application server only because it is generally not a good practice to allow the SAS Metadata Server to execute routine stored processes.
d. Select Store source code on application server because no other server needs to access the code.
e. Specify the physical folder where code is stored in Source code repository.
f. Both Stream and Package in Result capabilities are selected by default. Streaming refers to the process where the output is sent directly to the client without being stored on the server. It does not work well for graphical output. Packaging refers to process of consolidating results before sending them to the client.
Figure 2.3: Specify Execution Options in the Create New Stored Process Wizard
8. Complete the creation of the stored process, and then view the results of the Stored Process for Charges Lag Report.
Figure 2.4: Total Charges Lag Triangle Report
The report’s data layout is triangular in shape, hence the keyword “triangle” in its title. This data helps analysts in the Finance department estimate IBNR expenses.
Dates of service constitute rows of the report. Dates of payment span the columns. Consider the first row of the report. Healthy Living Inc. made payments for charges incurred in May 2010 for a period of six months. It made payments in May 2010 that accounted for a small fraction of the charges. In October 2010, it made payments that settled the company’s liability for May 2010.
The report enables analysts in the Finance department to calculate outstanding charges for May 2010 in any given month between May and October 2010. This is a simple algebraic computation derived by subtracting the sum of prior settled charges from total incurred charges.
The analysts in the Finance department now need to apply a historical ratio of paid/charged amounts to the outstanding charges to estimate IBNR expenses.
2.2.2 Calculating Historical Ratio of Paid/Charged Amounts
Instead of using SAS Enterprise Guide’s point-and-click interface, SAS code is used to create a stored process for calculating paid/charged amounts ratios.
Program 2.1 Calculating Paid/Charged Amounts Ratios
proc means data=dw.encounter noprint;
var AmtCharge AmtPaid;
class YearMonthPaidDt;
output out=Summary1 sum=;
where YearMonthPaidDt between “01MAY2010”d and “01JUN2012”d;
run;
data Summary1;
set Summary1;
where _TYPE_=1;
ratio=AmtPaid/AmtCharge;
drop _TYPE_ _FREQ_;
label Ratio='Paid to Charge Ratio'
run;
proc print data=Summary1;
var YearMonthPaidDt Ratio;
run;
A date in SAS is stored as the number of days since January 1, 1960. This is a number. However, it is fairly difficult for human brains to think of a date as a number. SAS allows users to specify dates as a literal or constant in the following manner, ‘01JAN1960’d. The ‘d’ informs the SAS compiler that it is a date literal versus another type of literal.
The code in Program 2.1 has a hardcoded date range. Substitute macro variables for hardcoded dates.
where YearMonthPaidDt between "&dt_min"d and "&dt_max"d;
Macro Variable Syntax | A macro variable in SAS is used to substitute text. The ampersand (&) indicates that a macro variable is being referenced. |
Range Prompts | The SAS BI prompting framework enables you to create prompts based on ranges (that is, values between the user-defined minimum value and maximum value of a variable). The range can be built from a numeric or a text variable. |
1. Convert the modified SAS code to a stored process. Under Project Tree, right-click on the code, and select Create Stored Process.
2. On page 4 of the Create New Stored Process wizard, select Prompt from SAS Code → dt_min. The Edit Prompt dialog box is displayed.
3. Create a prompt for Date.
a. On the General tab, enter a Name and Displayed text.
Figure 2.5: Create a Prompt in a Stored Process
b. On the Prompt Type and Values tab, select Date range as Prompt type. This creates several macro variables behind the scenes, including dt_min and dt_max. These macro variables exist in the code, so user input to the Date range prompt are captured and provided to the stored process.
Figure 2.6: Populate a Prompt in a Stored Process
4. Save and run the Stored Process for Paid Charge Amounts Ratio. Enter dates in the parameters. The calendar can be used as an aid.
Figure 2.7: Run the Stored Process for Paid Charge Amounts Ratio
5. The log confirms that the data has been correctly filtered.
>>> SAS Macro Variables:
DT_MAX=01Jun2012
DT_MIN=01May2010
Figure 2.8: Paid/Charged Amounts Ratio
The ratio can be multiplied by outstanding charges to derive an estimate of IBNR expenses. Assume that the outstanding charges for a given month are $2 million. A six-month moving average of the paid/charged amounts ratio is .5. The IBNR expenses for the month are estimated to be $1 million ($2 million x .5 = $1 million).
2.2. 3 Tracking Revenues, Costs, and Enrollment
To report on the financial health of the company, the Finance department at Healthy Living Inc. needs a graph showing updated trends in revenues, costs, and enrollment. Program 2.2 contains SAS code that tracks revenues, costs, and enrollment.
Program 2.2 Tracking and Displaying Revenues, Costs, and Enrollment
proc sql;
create table Revenue as
select YearMonthDOS, sum(Revenue)as Revenue,
count(distinct MemberId) as Members
from dw.Revenue
where YearMonthDOS between “&dt_min”d and “&dt_max”d
group by YearMonthDOS;
create table Cost as
select YearMonthDOS, sum(AmtPaid)as AmtPaid
from dw.encounter
where YearMonthDOS between “&dt_min”d and “&dt_max”d
group by YearMonthDOS;
create table Profits as
select a.YearMonthDOS, a.Revenue, a.Members, b.AmtPaid,
(a.Revenue/a.Members) as RevenuePM, (b.AmtPaid/a.Members) as AmtPaidPM
from Revenue a, Cost b
where a.YearMonthDOS=b.YearMonthDOS;
quit;
➊
symbol1 interpol=join height=10pt value=none line=1 width=2 ci=black;
symbol2 interpol=join height=10pt value=none line=1 width=2 ci=red;
symbol3 interpol=join height=10pt value=none line=1 width=2 ci=green;
legend1 frame;
Axis1 style=1 width=1 minor=none;
Axis2 style=1 width=1 minor=none;
Axis3 style=1 minor=none;
➋
proc gplot data=profits;
plot Revenue*YearMonthDOS AmtPaid*YearMonthDOS/
overlay vaxis=axis1 haxis=axis2 legend=legend1;
plot2 Members*YearMonthDOS=3/
overlay vaxis=axis3 overlay legend=legend1;
run;
quit;
➊ The SYMBOL statement gives the GPLOT procedure information about drawing the plot. For example, interpol=join creates a line graph. Three SYMBOL statement are used for three lines.
➋ The PLOT2 statement uses SYMBOL3.
In PROC SQL, you can group multiple queries in one invocation of the procedure. This is efficient because SAS uses resources (memory) each time it invokes the procedure.
2.3 Advanced Stored Processes
The analysts in the Finance department at Healthy Living Inc. have a voracious appetite for reports analyzing costs. They like to see trends in costs at various levels of detail. Once they locate outlier costs, they investigate underlying claims.
This section creates two stored processes that allow Finance analysts to trend costs and analyze outlier claims.
1. Costs by Category and Diagnoses Codes contains prompts that are dynamically linked to one another.
2. Costs by Diagnosis Code Summary enables analysts to analyze monthly costs for a specified diagnosis code. This stored process is connected to another stored process that lists individual claims, enabling analysts to home in on outlier data.
2.3.1 Creating a Stored Process with Linked Prompts
Finance analysts like to see costs by category and diagnosis codes. The values of diagnosis codes depend on the value of category. As a result, the stored process needs to have two prompts that are linked to one other.
Prompt linking does not affect underlying code. Because there are two prompts, two macro variables need to be created in the code, one for Category and the other for Diagnosis.
Program 2.3 Costs by Category and Diagnosis Codes
proc sql;
create table CostTrends as
select YearMonthDOS, Category, Diagnosis, sum(AmtPaid) as AmtPaid
from dw.encounter
where Status=‘Paid’ and Category=“&ValCat” and Diagnosis=“&ValDiag”
group by YearMonthDOS, Category, Diagnosis;
proc print data=CostTrends;
run;
Diagnosis is a computed column in the Encounter table. It contains ICD-9 codes for ER visits and DRG codes for inpatient admissions. Inpatient admissions have associated ICD-9 codes as well. However, DRG codes are used for reporting and clinical purposes for inpatient admissions only.
1. Convert Program 2.3 to a stored process. Under Project Tree, right-click on the code, and select Create Stored Process.
2. On page 4 of the Create New Stored Process wizard, select Prompt from SAS Code → Multiple. The Edit Prompt dialog box is displayed.
3. Create a prompt forCategory.
a. On the General tab, enter a Name and Displayed text.
b. On the Prompt Type and Values tab, select the values shown in Figure 2.9.
i. Select Text for Prompt type.
ii. Select User selects values from a dynamic list for Method for populating prompt. The first prompt in a sequence of linked prompts can derive its values from a static list. However, subsequent prompts must derive their values dynamically.
Figure 2.9: Dependent Prompts in a Stored Process
4. Create a prompt for Diagnosis, repeating step 3.
5. On the Dependencies tab, specify links between Category and Diagnosis.
Figure 2.10: Specify Dependencies between Prompts in a Stored Process
6. Save and run the Stored Process for Cost by Category Dependent Prompts. Choosing a Category populates pertinent diagnosis codes.
Figure 2.11: Run the Stored Process for Cost by Category Dependent Prompts
Prompts with dependencies ensure that zero rows are not returned for any user query. This can be powerful functionality for users because they do not need to worry about finding combinations of prompt values with data.
The only disadvantage of linked prompts is that they tend to slow down the selection process. This is expected because linked prompts are based on dynamic queries. To speed up queries, the BI architect is encouraged to build linked prompts from lookup or dimensional tables, not from the original data source (as demonstrated in this section).
2.3.2 Chaining Stored Processes
Analysts in the Finance department at Healthy Living Inc. often need to explain outlier costs. This section shows how stored processes can be chained together so that the analysts can jump seamlessly from aggregate to detail data.
Although the stored process created in the previous section is a good candidate to be linked to a detailed stored process, a simpler stored process is created to be used as an aggregate-level stored process.
Stored Process for Cost Trends Summary, the aggregate-level stored process, requires the user to enter a diagnosis code. The user prompt is connected to ValDiag, a macro variable in the stored process.
Figure 2.12: Stored Process for Cost Trends Summary
This stored process is run by a SAS BI web tool called the SAS Stored Process Web Application. The URL for this tool is of the form: http://<server name>:8080/SASStoredProcess. Later in the book, you will access and run stored processes through Microsoft Office and SAS web tools such as SAS Web Report Studio and SAS Information Delivery Portal.
The results of the stored process enable the user to retrieve detailed results for a given month by clicking Detailed Report.
Figure 2.13: Chained Stored Processes
The URLLink column is constructed in the code for the Stored Process for Cost Trends Summary. The code for this stored process is shown in Program 2.4.
Program 2.4: Chaining Stored Processes
proc sql;
create table CostTrends as
select YearMonthDOS, Diagnosis label='Diagnosis', round(sum(AmtPaid)) as
AmtPaid format=comma15.0,
“<a href='http://&_srvname:&_srvport/SASStoredProcess/do?%nrstr(&_program)=/Shared Data/STP/
Stored+Process+for+Cost+Trends+Detail%nrstr(&ValDiag)=&ValDiag"||"%nrstr(&Yea
rMonthDOS)="||strip(put(YearMonthDOS,Date9.))||"'>Detailed Report</a>" as
URLLink length=500 format=$500. label="Link"
from dw.encounter
where Diagnosis="&ValDiag"
group by YearMonthDOS, Diagnosis;
quit;
proc print data=CostTrends;
run;
Focus on the part of the code that creates URLLink. This code appears to be long and convoluted, but a little knowledge about HTML and SAS automatic macros enables programmers to understand it.
1. HTML has a wide array of tags that describe different types of document content. For example, the <header> tag describes header information. Another tag, the <a> tag, defines links. This tag has a begin value (<a) and an end value (/a>).
2. Tags have attributes or characteristics. The href attribute in the <a> tag specifies the link address, which is enclosed in quotation marks. If a link address contains double quotation marks, then it is enclosed in single quotation marks.
3. A plus sign (+) indicates space in HTML.
4. A stored process has several automatic macros available for use. These macros begin with an underscore. They are not to be used elsewhere. The automatic macros of interest include:
a. _srvname: The name of the server. This resolves to the server where SAS Stored Process Web Application is installed.
b. _srvport: This value is usually 8080.
c. _program: This resolves to the name of the SAS stored process, including its location. The link address connects to the SAS Stored Process Web Application and tells it to execute a process called Stored Process for Cost Trends Detail with user inputs of ValDiag and YearMonthDOS.
• The value of ValDiag is known because this is entered by the user before running the aggregate-level stored process.
• The value of YearMonthDOS is captured from the results of the aggregate-level stored process. However, it needs some manipulation before it can be placed in a macro. Because YearMonthDOS is a number, and SAS macro variables accept only text, it is converted to a character field with the PUT function. After it is converted, the STRIP function is applied to remove any leading or trailing blanks.
NRSTR is a macro function used to mask characters that are special to the SAS macro language. Without this masking function, characters like the ampersand (&) and percent sign (%) are automatically resolved. For example, &ValDiag=&ValDiag resolves to 599=599. However, the result that is wanted is the text &ValDiag=599. The NRSTR function allows the link to be built to include the names and values of prompts.
The code for the Stored Process for Cost Trends Detail is provided in Program 2.5.
Program 2.5: Code for the Stored Process for Cost Trends Detail
proc sql;
create table CostTrendsDetail as
select DOS, Category, Diagnosis label='Diagnosis', Status,
ClaimID, MemberID, ProviderID,
AmtPaid format=comma15.0, AmtCharge format=comma15.0
from dw.encounter
where Diagnosis="&ValDiag" and YearMonthDOS="&YearMonthDOS"d
group by MonthDOS, Diagnosis;
quit;
title “Claim Detail";
proc print data=CostTrendsDetail;
run;
2.4 Modifying a Stored Process
The Create New Stored Process wizard in SAS Enterprise Guide can be used to modify an existing stored process. Let’s add the following code to Program 2.3:
proc means data=CostTrends sum;
var AmtPaid;
run;
1. To modify the existing stored process, right-click on the stored process under Project Tree, and select Modify Stored Process for Cost by Category Dependent Prompts.
2. The Stored Process Manager provides an easy interface to modify the stored process. To update the code, click SAS Code. SAS realizes that the server code and SAS Enterprise Guide code are different because the stored process was created by right-clicking on Program 2.3. SAS allows you to choose between the SAS Enterprise Guide code or the server code.
Figure 2.14: Modify a Stored Process
Click Use EG Code to accept modifications to the existing stored process.
2.5 Creating Stored Processes with Complex Macro Programming
Prompts in SAS stored processes are linked to code by macro variables. This section incorporates complex macro programming to build flexible prompts. These prompts enable users to do the following:
1. Select multiple values.
2. Select all possible values.
2.5.1 Multiple Values Selection in Prompt
When multiple values are selected in a prompt, SAS creates macro variables by default. These macro variables include one that counts the number of values chosen and several child macro variables that contain the values chosen.
Stored process code needs to be correctly set up to accommodate the macro variables created by the prompt.
Program 2.6: Creating Macro Code to Select Multiple Values in a Prompt
/*Macro Options*/
Options mprint symbolgen mlogic;
/*Beginning of Macro Shell*/
%macro parse;
proc sql;
create table RevenuebyMonth as
select YearMonthDOS, sum(Revenue) as Revenue
from dw.revenue
where type in
( %if &value_count. = 1 %then %do;
"&value"
%end;
%else %do Num = 1 %to &value_count.;
"&&Value&Num."
%end;
)
group by YearMonthDOS;
quit;
proc print data=RevenuebyMonth;
run;
%mend parse;
/*End of Macro Shell*/
/*Macro Invocation*/
%parse
Here are descriptions of different elements in the macro code:
| Code Element | Explanation |
|---|---|
| macro options | Are excellent for debugging, but they should be removed when development is finalized because they use resources and fill up log files. |
| mprint | Displays SAS statements generated by macro execution. |
| mlogic | Displays the results of resolving macro variable references. |
| symbolgen | Determines when macro execution begins and ends, including the status and resolution of any %if and %do conditions. |
| macro shell | Sandwiches SAS statements in one macro. |
| macro invocation | Calls the macro shell. |
| %if…%then…%do | Is conditional macro syntax. |
| &&Value&Num | && allows for repeated resolution of macro variables. The first resolution resolves to &Value1, &Value2. The second resolution resolves to values selected by the user in the prompt. |
1. Create a stored process from Program 2.6.
2. Create a new prompt. On the General tab, enter information as shown in Section 2.2.2.
3. On the Prompt Type and Values tab, enter information as shown in Figure 2.15.
a. Select User selects values from a static list for Method for populating prompt because it is faster.
b. Select Multiple values for Number of values so that the user can select more than one value.
c. Click Get Values. This enables the programmer to create a list of values from the appropriate table and column.
Figure 2.15: Specify Multiple Values in a Stored Process Prompt
4. Save and run the stored process. Select both values for type.
Figure 2.16: Select Multiple Values in the Stored Process for Revenue by Month
5. Verify from the log that both values of type are included.
MPRINT(PARSE): create table RevenuebyMonth as select YearMonthDOS,
sum(Revenue) as Revenue from dw.revenue where type in ( "Adjusted"
"Original" ) group by YearMonthDOS;
2.5.2 All Possible Values Selection in Prompt
This section modifies Program 2.6 to create a stored process that allows the user to select all possible values in a prompt.
Program 2.6: Modifying Code to Allow All Possible Values Selection
options symbolgen mlogic mprint;
%macro parse;
proc sql;
create table RevenuebyMonth as
select YearMonthDOS, sum(Revenue) as Revenue
from dw.revenue
where type in
( %if &value=_ALL_VALUES_ %then %do;
"Adjusted" “Original"
%end;
%else %if &value_count. = 1 %then %do;
"&value"
%end;
%else %do Num = 1 %to &value_count.;
"&&Value&Num."
%end;
)
group by YearMonthDOS;
quit;
proc print data=RevenuebyMonth;
run;
%mend parse;
%parse
1. On the Prompt Type and Values tab, select All possible values.
Figure 2.17: Include All Possible Values in a Prompt
2. Save and run the stored process.
3. Check the log and verify that the macro variables resolved correctly.
VALUE=_ALL_VALUES_
VALUE_COUNT=1
MPRINT(PARSE): proc sql;
SYMBOLGEN: Macro variable VALUE resolves to _ALL_VALUES_
MLOGIC(PARSE): %IF condition &value=_ALL_VALUES_ is TRUE
MPRINT(PARSE): create table RevenuebyMonth as select YearMonthDOS,
sum(Revenue) as Revenue from dw.revenue where type in ( "Adjusted"
"Original" ) group by YearMonthDOS;
2.6 Conclusion
SAS stored processes are a powerful feature of the SAS BI Platform. They save programmers valuable time by creating self-serve reporting. The next chapter examines how SAS stored processes are used from Microsoft Office.