DATA Step, Macro, Functions and more

Member enroll and drop with Merge statement

Reply
Occasional Contributor
Posts: 13

Member enroll and drop with Merge statement

[ Edited ]

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

Super User
Posts: 5,069

Re: Member enroll and drop with Merge statement

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.

Occasional Contributor
Posts: 13

Re: Member enroll and drop with Merge statement

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

Super User
Posts: 17,730

Re: Member enroll and drop with Merge statement

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. 

Ask a Question
Discussion stats
  • 3 replies
  • 187 views
  • 0 likes
  • 3 in conversation