How do I create a cumulative median field in SAS V

问题:

Consider a List Table in VA 7.4 Designer. It has only two fields (Month, Median Patients). The desire is to calculate the cumulative median over the months. This cumulative median is labelled as "Median Patients". An example data with more descriptive steps is attached.

Please, take look. Note: It's easy to calculate the monthly Median Patients by just using the median function in VA 7.4 Designer (not the desire). The cumulative median is desired.

Thank you and have fun.`

Sample Data
-------------
Account ID  Month       #patients
----------  -------     ----------
1       Jan2017     5
2       Jan2017     3
3       Feb2017     7
4       Feb2017     6
5       Feb2017     2
6       Mar2017     4
7       Apr2017     1
8       Apr2017     10
9       Apr2017     9
10      Apr2017     3



Typical calculation in SAS VA 7.4
-----------------------------------
Monthly Median (Easy using median function)
-------------------------------------------
Month       Median Patients
---------   ---------------     
Jan2017     4       ( 5+3 ) /2
Feb2017     6       middle of ( 2,6,7 )
Mar2017     4       
Apr2017     6       middle of ( 1,3,9,10 )  = (3+9)/2 = 6



Cumulative Monthly Median (Desired in SAS VA 7.4) Any idea how to calculate this assuming this is in a List Table with only two fields (Month and Median Patients)?
------------------------------------------------------------------------------------------------------------------------------------------------------------
Month       Median Patients
--------    -----------------
Jan2017     4       ( 5+3 ) /2
Feb2017     5       middle of ( 2,3,5,6,7 )     = 5
Mar2017     5       middle of (2,3,4,5,6,7 )    = (4+5) /2 = 4.5(approx. 5 when rounded)
Apr2017     5       middle of(1,2,3,3,4,5,6,7,9,10) = (4+5) /2 = 4.5(approx. 5 when rounded)

回答1:

In VA Designer:

1. Create a date parameter called monthyear and assign it same format as you Month field,
2. In the body of your report Create a drop-down & List-Table objects,
3. Assign the Month field to the drop-down, then go to roles tab and in the parameter box select monthyear ; which you just created,
4. Select the List-Table, assign Month, Patient (make sure the aggregation is median) then go to the filters tab and create advances filter using the point and click: Month <= monthyear
5. Now the interaction is setup, make sure when you select a date from drop down you see the correct data to calculate, monthly median will be calcualted

6. Remove the Month/Date field from the table then the Cumulative Median will be calculated

回答2:

To do this via SAS Code; I Left Join the segregated data to itself on month <= month (to calculate the cumulative median for each month).

Data:

data have;
 infile datalines dlm=',' dsd;
 informat Month monyy7.;
 format Month monyy7.;
 input Account_ID  Month    patients;
 datalines;
1,Jan2017,5
2,Jan2017,3
3,Feb2017, 7
4,Feb2017,6
5,Feb2017, 2
6,Mar2017 , 4
7,Apr2017,1
8,Apr2017,10
9,Apr2017, 9
10, Apr2017 ,3
;
run;

Cumulative Median:

proc sql;
create table want as 
select t1.Month , median(t2.patients) as Cumm_Median , round(median(t2.patients)) as Cumm_Median_rounded
from have as t1 left join have as t2
on t2.Month le t1.month
group by t1.month
order by t1.Month
;
quit;

Output:

 Month=JAN2017 Cumm_Median=4 Cumm_Median_rounded=4
 Month=FEB2017 Cumm_Median=5 Cumm_Median_rounded=5
 Month=MAR2017 Cumm_Median=4.5 Cumm_Median_rounded=5 
 Month=APR2017 Cumm_Median=4.5 Cumm_Median_rounded=5