cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
J_Brown
‎03-23-2020
  • 2 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Calculating a rolling median with varying numbers of observations

by Calcite | Level 5 in SAS Programming
‎03-13-2020 02:03 PM
‎03-13-2020 02:03 PM
Sorry, the data for each month is quite extensive so I just posted the first few lines.   An input (very condensed) would look something like this: AUG2015       26 AUG2015       27 SEP2015       16 SEP2015       20 SEP2015        7 OCT2015       22 NOV2015       28 NOV2015       28 NOV2015       35 NOV2015       15 DEC2015       28 DEC2015         8 DEC2015       14   The idea is to obtain something like this: AUG2015     .. SEP2015     .. OCT2015    21 NOV2015    21 DEC2015    25   As I mentioned in my original comment I think what I need to do is assign a group to each month, something along the lines of: data outfile_test3; set outfile_test2; by END_DATE; retain count 0; if first.END_DATE then count=count+1;   This begins to count, but it counts the day as a new date (so it adds a count for the 2nd Aug 2015 etc). I can make a new variable using either MONTH or YEAR functions and it seems to be working correctly, but I'm not sure exactly how to combine the two.   I'm also not sure exactly how to calculate the 'window' of medians. I think the code I'm after is using a lag function but I don't really know how to write it.   Sorry for how basic this work through is, but I am very new SAS! ... View more

Calculating a rolling median with varying numbers of observations

by Calcite | Level 5 in SAS Programming
‎03-13-2020 12:57 PM
‎03-13-2020 12:57 PM
I have a problem that is ostensibly simple, but I'm really struggling with it.   In my data I have two columns that are important: END_DATE and DURATION. END_DATE is a date variable formatted as monyy7 (but does contain the day of the month as well).  The data looks like this: AUG2015       26 AUG2015       27 AUG2015       16 AUG2015       20 AUG2015        7 AUG2015       22 AUG2015       28 AUG2015       28 AUG2015       35 AUG2015       15 AUG2015       28 AUG2015         8 AUG2015       14 With these I want to calculate a 3 month moving median. It's easy to calculate the median for the months, but I'm not sure how to do a rolling median. From googling I found PROC EXPAND, but this requires non-duplicate variables. This is a problem as for each month we have lots of durations (as you can see from the example). I can't calculate the median for each month and then use PROC EXPAND as a median of a median won't be an accurate measure. I think I need create a new variable where each month is assigned a group but I'm unsure how to do this. ie. AUG2015 =0, SEP2015 =1, OCT2015=2 etc Once I have that I assume that I could calculate the rolling median using some sort of lag function?   I'm new to SAS, so apologies if this question is very trivial! ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎03-23-2020 08:59 AM
Quick links