"Moving Distinct" Counters

[JS]

Hello Friends!

I'm trying to create a "Moving Distinct Variable" counter by ID.

Basically, I have a dataset which contains transaction records which tracks user changes in their account profile. I want to build in a variable that tracks how many distinct emails the user has had an that specific point in time, and another variable that indicates whether or not that email was the first time we've seen it, or if they're switching back to an old one they had before.

ID Date email_address

josh 1/1/2015 josh1@gmail.com
josh 1/2/2015 josh2@yahoo.com
josh 1/3/2015 josh3@yahoo.com
mary 1/4/2015 mary123@aol.com
mary 1/5/2015 mars@blah.com
josh 1/6/2015 josh1@gmail.com
josh 1/7/2015 josh2@yahoo.com
josh 1/8/2015 josh3@yahoo.com
mary 1/9/2015 mary123@aol.com
mary 1/10/2015 mars@blah.com

ID Date email_address distinct_emails_for_user first_time_email_seen_with_user

josh 1/1/2015 josh1@gmail.com 1 YES
josh 1/2/2015 josh2@yahoo.com 2 YES
josh 1/3/2015 josh3@yahoo.com 3 YES
mary 1/4/2015 mary123@aol.com 1 YES
mary 1/5/2015 mars@blah.com 2 YES
josh 1/6/2015 josh1@gmail.com 3 NO
josh 1/7/2015 josh2@yahoo.com 3 NO
josh 1/8/2015 josh3@yahoo.com 3 NO
mary 1/9/2015 mary123@aol.com 2 NO
mary 1/10/2015 mars@blah.com 2 NO

[Haikuo]

You sure can try it using Hash object, Proc SQL is just simpler to code:

data have;
	input ID$ Date:mmddyy10. email_address: $40.;
	format date mmddyy10.;
	cards;
josh 1/1/2015 josh1@gmail.com
josh 1/2/2015 josh2@yahoo.com
josh 1/3/2015 josh3@yahoo.com
mary 1/4/2015 mary123@aol.com
mary 1/5/2015 mars@blah.com
josh 1/6/2015 josh1@gmail.com
josh 1/7/2015 josh2@yahoo.com
josh 1/8/2015 josh3@yahoo.com
mary 1/9/2015 mary123@aol.com
mary 1/10/2015 mars@blah.com
;

proc sql;
	create table want as
		select *, (select count(distinct email_address) from have where id=a.id and date le a.date) as distinct_emails_for_user,
		(select 
			case 
				when sum(email_address=a.email_address)>1 then 'NO' 
				ELSE 'YES' 
			END  
		from have where id=a.id and date le a.date) 
			As first_time_email_seen_with_user
				fROM HAVE A
	;
QUIT;

[JS]

Hey Haikuo!

Thank you so much!

That's a really intuitive answer to the problem, and gets me exactly what I need. Thank you so much.

Jason

[JS]

Thanks again for the help.

The issue that I am running into at the moment is runtime.

The table that I am attempting to draw from has approximately 1.8M records. I pulled stopped the query early to see what progress was made after 1M (see below). At this rate, it would take (1.8M Records / 15 Records in 1 minutes= 120000 minutes, 120000 minutes/ 60 minutes / 24 hours) ~83 Days to complete.

Do you see any optimization opportunities here?

JS

WARNING: The data set ACCT may be incomplete. When this step was stopped there were 15 observations and 25 variables.
NOTE: Compressing data set ACCT increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
WARNING: Data set ACCT was not replaced because this step was stopped.
NOTE: PROCEDURE SQL used (Total process time):
real time 1:00.26
cpu time 1:00.21

[Ksharp · Accepted solution]

data have;
	input ID $ Date:mmddyy10. email_address: $40.;
	format date mmddyy10.;
	cards;
josh 1/1/2015 josh1@gmail.com
josh 1/2/2015 josh2@yahoo.com
josh 1/3/2015 josh3@yahoo.com
mary 1/4/2015 mary123@aol.com
mary 1/5/2015 mars@blah.com
josh 1/6/2015 josh1@gmail.com
josh 1/7/2015 josh2@yahoo.com
josh 1/8/2015 josh3@yahoo.com
mary 1/9/2015 mary123@aol.com
mary 1/10/2015 mars@blah.com
;
run;
data want;
 if _n_ eq 1 then do;
  declare hash hh();
  hh.definekey('id');
  hh.definedata('count');
  hh.definedone();
  declare hash h();
  h.definekey('id','email_address');
  h.definedone();
 end;
 set have;
 length flag $ 8; 
 if h.find()=0 then do;
   hh.find();flag='NO';
 end;
 else do;
  flag='YES';
  if hh.find()=0 then count+1;
   else count=1;
 h.replace();hh.replace();
 end;
run;

[JS]

Thanks Xia Keshan.

Would this be preferred over the SQL counterpart? Do you see one better than the other?

Thanks.

[Ksharp]

Yeah. SQL is not good for Big Table due to calculated at very obs(record). If Table is small , both could be accepted.