topic Re: count of repeat contacts fromk previous days in SAS Enterprise Guide

count of repeat contacts fromk previous days

DME790 — Tue, 06 Nov 2018 01:53:03 GMT

HI All,

I have a data set which I need to calculate any persons that made multiple contact on that day as well as made contact in the previous seven days.

For example I have a list of all contacts made on 5 November 2018 and I need a count of those 'Persons' that also made contact between the 29 October 2018 and 4 November 2018.

I need the total count of contacts (in example is 10)
Distinct number of contacts (in the example is 9)
the count of duplicate person who made contact on the previous seven days
Then I can calculate the percentage of repeat contacts

data test;
input Date :yymmdd. DN $ Person $;
format date yymmdd10.;
datalines;
2018-11-05 Apple AA
2018-11-05 Pear BB
2018-11-05 Peach CC
2018-11-05 Apple AA
2018-11-05 Apple DD
2018-11-05 Apple EE
2018-11-05 Pear FF
2018-11-05 Peach GG
2018-11-05 Peach HH
2018-11-05 Pear II
2018-11-02 Apple DDD
2018-11-02 Pear EEE
2018-11-02 Peach FFF
2018-11-02 Apple GGG
2018-11-02 Apple HHH
2018-11-02 Apple DD
2018-11-02 Pear III
2018-11-02 Peach FF
2018-11-02 Peach JJJ
2018-11-02 Pear KKK
2018-11-01 Pear JJ
2018-11-01 Apple AA
2018-11-01 Peach KK
2018-11-01 Pear BB
2018-11-01 Peach LL
2018-11-01 Pear MM
2018-11-01 Apple EE
2018-11-01 Apple NN
2018-11-01 Pear OO
2018-11-01 Peach PP
2018-10-31 Pear QQ
2018-10-31 Apple RR
2018-10-31 Peach SS
2018-10-31 Pear FF
2018-10-31 Peach TT
2018-10-31 Pear UU
2018-10-31 Apple AA
2018-10-31 Apple VV
2018-10-31 Pear WW
2018-10-31 Peach XX
2018-10-30 Apple AA
2018-10-30 Pear AAA
2018-10-30 Peach CC
2018-10-30 Apple BBB
2018-10-30 Apple DD
2018-10-30 Apple EE
2018-10-30 Pear FF
2018-10-30 Peach GG
2018-10-30 Peach HH
2018-10-30 Pear ZZ
2018-10-29 Pear QQ
2018-10-29 Apple RR
2018-10-29 Peach SS
2018-10-29 Pear FF
2018-10-29 Peach TT
2018-10-29 Pear UU
2018-10-29 Apple AA
2018-10-29 Apple VV
2018-10-29 Pear WW
2018-10-29 Peach XX;
run;

so from the example I should end up with the following result.

Percentage of multiple contacts

	5/11/2018	2/11/2018	1/11/2018	31/10/2018	30/10/2018	29/10/2018	total Repeat %
Monday, 5 November 2018	11%	22%	33%	22%	78%	22%	89%

I have attached the spreadsheet with the test data and calculations - I'm not sure how to get the same results out of SAS EG.

Any help appreciated

Cheers

Dean

Re: count of repeat contacts fromk previous days

DME790 — Tue, 06 Nov 2018 02:41:51 GMT

Have added the following code that gets me the same day repeat Persons.

data test2;
	set test;
	Where date = '05Nov2018'd;
run;

proc sql;
	Create table testCallPersons as
		Select Date, Person, count(person) as personcount
			From (select distinct date, Person from Test2)
				Group by Date, Person;
quit;

proc Summary data=testCallPersons;
	By Date;
	VAr personcount;
	Output out=testPCount (Drop=_freq_ _Type_) sum=;
run;

proc sql;
	Create table testCallCount as
		Select Date, Person, count(person) as Callcount
			From (select date, Person from Test2)
				Group by Date, Person;
quit;

Data TestDupPerscount;
	Set testCallCount;

	If Callcount >1 Then
		DupCount = 2;
	Else DupCount = 1;
run;

proc Summary data=TestDupPerscount;
	By Date;
	VAr Callcount	DupCount;
	Output out=testCCount (Drop=_freq_ _Type_) sum=;
run;

Data testMerge;
	Merge testPCount  testCCount;
	By Date;
run;

Data testPerc;
	Drop personcount Callcount DupCount;
	Set testmerge;
	Day1Perc = (DupCount - personcount) / personcount;
	format Day1perc percent7.2;
run;

Anyway of doing this smarter? Just now need to work out how to match against the previous days to get the duplicates.

Cheers

Dean

Re: count of repeat contacts fromk previous days

Reeza — Tue, 06 Nov 2018 03:05:54 GMT

Here's one approach that's simpler. It matches the 11.1% you reported via your method.

proc freq data=test noprint;;
where date='05Nov2018'd;
table person / out=t1;
run;

proc format;
value dup_fmt
1 = 'Unique'
2-high = 'Duplicated';
run;

proc freq data=t1 (rename=count=Freq) noprint;;
table freq / out=t2;
format freq dup_fmt.;
run;

@DME790 wrote:

Have added the following code that gets me the same day repeat Persons.

data test2;
	set test;
	Where date = '05Nov2018'd;
run;

proc sql;
	Create table testCallPersons as
		Select Date, Person, count(person) as personcount
			From (select distinct date, Person from Test2)
				Group by Date, Person;
quit;

proc Summary data=testCallPersons;
	By Date;
	VAr personcount;
	Output out=testPCount (Drop=_freq_ _Type_) sum=;
run;

proc sql;
	Create table testCallCount as
		Select Date, Person, count(person) as Callcount
			From (select date, Person from Test2)
				Group by Date, Person;
quit;

Data TestDupPerscount;
	Set testCallCount;

	If Callcount >1 Then
		DupCount = 2;
	Else DupCount = 1;
run;

proc Summary data=TestDupPerscount;
	By Date;
	VAr Callcount	DupCount;
	Output out=testCCount (Drop=_freq_ _Type_) sum=;
run;

Data testMerge;
	Merge testPCount  testCCount;
	By Date;
run;

Data testPerc;
	Drop personcount Callcount DupCount;
	Set testmerge;
	Day1Perc = (DupCount - personcount) / personcount;
	format Day1perc percent7.2;
run;

Anyway of doing this smarter? Just now need to work out how to match against the previous days to get the duplicates.

Cheers

Dean