05-04-2017 11:08 PM
Okay I am extremely new to SAS VA and not quite sure of where and how best to do the data preparation steps. I have a static report which I created SAS EG which shows the number of memberships active per time period (can do weekly, monthly, etc) based on the membership start and end dates.
For example, if the period of interest starts at t0 then I basically do:
1. Determine number of members at t0; count(*) where member_start < t0 and member_end > t0
2. For each week (or month), get number of new members, e.g.
ods output onewayfreqs=incoming_members; proc freq data=ds; tables member_start; format member_start weeku5. run; ods close;
3. For each week (or month), get number of leaving members, e.g, :
ods output onewayfreqs=exiting_members; proc freq data=ds; tables member_end; format member_end weeku5.; run; ods close;
I then merge the datasets and set active_members = opening + incoming_members + exiting_members per week. There are a few extra steps to consider weeks with no new/leaving members, etc.
So my question is, what is the best way to do this in SAS VA?
Suggestions on best way to tackle this problem will be greatly appreciated. I have attached an example data set with calculations in Excel.