BookmarkSubscribeRSS Feed
DME790
Pyrite | Level 9

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.  

 

  1. I need the total count of contacts (in example is 10)
  2. Distinct number of contacts (in the example is 9)
  3. the count of duplicate person who made contact on the previous seven days  
  4. 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/20182/11/20181/11/201831/10/201830/10/201829/10/2018total Repeat %
Monday, 5 November 201811%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

 

 

 

 

2 REPLIES 2
DME790
Pyrite | Level 9

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

 

 

Reeza
Super User

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

 

 


 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2 replies
  • 812 views
  • 1 like
  • 2 in conversation