SAS VA 7.4 Designer中如何创建累积中位数字段？

Got it, let's break down how to get that cumulative monthly median you need in SAS Visual Analytics 7.4. First, let's recap the core requirement: you want a List Table showing each month and the median of all #patients values from the first month up to the current month (not just the median for the single month), named "Median Patients".

Key Background #

First, a critical note: you'll need to work with the raw, account-level data (with Account ID, Month, and #patients fields) rather than pre-aggregated monthly data. If you only have the monthly median values, you can't reconstruct the cumulative median because you've lost the underlying patient count details.

Step-by-Step Solution #

1. Prepare Your Data Source
   Ensure your dataset includes the raw #patients values for each account and month. This is non-negotiable—cumulative median relies on having all historical individual patient counts to compute against.

2. Create a Custom Calculated Field
   In SAS VA Designer, navigate to the Data pane, right-click your dataset, and select New Calculated Field. Name this field Median Patients (to match your requirement) and use this window function expression:

   median(#patients) over (
       order by Month 
       rows between unbounded preceding and current row
   )
   

   Let's break this down:

   • median(#patients): Computes the median of the patient count values
   • over (order by Month): Orders the data chronologically by month
   • rows between unbounded preceding and current row: Tells SAS VA to include every row from the start of the dataset up to the current month's rows when calculating the median

3. Add Rounding (If Needed)
   If you want to round the result to a whole number (like your example shows 4.5 rounded to 5), wrap the expression in round():

   round(
       median(#patients) over (
           order by Month 
           rows between unbounded preceding and current row
       ),
       1
   )
   

4. Build Your List Table
   Drag the Month field and your new Median Patients calculated field into the List Table. Make sure Month is set to a grouped role (so you get one row per month), and the calculated field is set to an aggregated role (though the window function already handles the cumulative calculation).

Verify with Your Sample Data #

Let's check this against your example to confirm it works:

• Jan2017: Uses values [5,3] → median = 4
• Feb2017: Uses values [5,3,7,6,2] → sorted [2,3,5,6,7] → median = 5
• Mar2017: Adds [4] → sorted [2,3,4,5,6,7] → median = 4.5 (rounded to 5)
• Apr2017: Adds [1,10,9,3] → sorted [1,2,3,3,4,5,6,7,9,10] → median =4.5 (rounded to5)

This matches exactly what you're looking for!

Important Notes #

• Double-check that your Month field is formatted as a date or ordered categorical variable. If it's just text (like "Jan2017"), ensure SAS VA recognizes the chronological order—you might need to adjust the sort order in the List Table settings.
• If you're working with pre-aggregated data (only monthly medians), you'll need to go back to the raw dataset. Aggregated monthly medians don't contain enough information to compute the cumulative median of the original patient counts.

内容的提问来源于stack exchange，提问作者ASH