BookmarkSubscribeRSS Feed
fannavivian
Obsidian | Level 7

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

3 REPLIES 3
Astounding
PROC Star

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.

fannavivian
Obsidian | Level 7

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

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1063 views
  • 0 likes
  • 3 in conversation