12-29-2016 09:56 PM - edited 12-29-2016 09:57 PM
I have a question about member enrolling and dropping. Suppose there are Jan, Feb, March, ... Dec 2016 member data, total 12 dataset. In each dataset, variables are the same (>100 variables). Would like to see who drops and who enrolls. My code is as following:
%LET measure = var1;
data enroll (keep=MEMBERID &measure flag) drop (keep=MEMBERID &measure flag);
merge Jan2016Data (keep=MEMBER &measure IN=a) Feb2016Data (keep=MEMBER &measure IN=b);
if b then flag="enroll";
if a then flag="drop";
if &measure ^= ""; /*there are many missing value which need to be excluded basing on the requirement*/
if a ^= b;
if flag="enroll" then output enroll;
if flag="drop" then output drop;
1. how to make &measure in Feb2016Data not to overwrite Jan2016Data?
2. Why sas result is different than my manual result (compared in excel)? Is there anything wroing? (no error msg in log)
12-29-2016 10:53 PM
Regarding question #1, there is no need to make any changes. You later select observations where MEMBER did not appear in both data sets (a ^= b), so the remaining observations will not face any overwriting.
Regarding question #2, you haven't shown us your expectations. Give just one example (a single MEMBER) and show us what you expect and what happened in the SAS program.
12-29-2016 11:58 PM
I would consider a different algorithm as that won't scale.
Consider appending data instead of merging and using INDSNAME to obtain source.
set jan2016 (keep=memberId)
input_dataset = source;
You can then use the file above to prepare summaries. Note that you'll need previous months record to determine who is new in January.