topic Re: Member enroll and drop with Merge statement in SAS Programming

Member enroll and drop with Merge statement

fannavivian — Fri, 30 Dec 2016 02:57:28 GMT

Hi guys,

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);
     by member;
     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;
run;

Issure:

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)

Thanks!!

Viv

Re: Member enroll and drop with Merge statement

Astounding — Fri, 30 Dec 2016 03:53:59 GMT

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.

Re: Member enroll and drop with Merge statement

Reeza — Fri, 30 Dec 2016 04:58:32 GMT

I would consider a different algorithm as that won't scale.

Consider appending data instead of merging and using INDSNAME to obtain source.

Data monthly;

set jan2016 (keep=memberId)

feb2016(keep=memberid)

...

dec2016(keep=memberid) INDSNAME=source;

input_dataset = source;

run;

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.

Re: Member enroll and drop with Merge statement

fannavivian — Fri, 30 Dec 2016 14:54:58 GMT

question #1 - Yeah, your are absolutely right! I forgot that point.