Fluorite | Level 6

## Calculate the number of new clients

Hi

Below is a sample of the data.

I am trying to get the number of new clients that is defined as clients who has not been seen in the 5 years preceding the first service contact during the reference period.

For example, in 2021 there were two new clients A001 and E001 (within five years).

ClientD ContactDate
A001 01/11/2010
A001 02/11/2010
A001 04/11/2010
B001 05/06/2016
B001 07/06/2016
B001 08/06/2016
C001 08/02/2017
D001 01/03/2014
A001 03/11/2021
A001 04/11/2021
E001 02/04/2021
G001 …… (missing)
C001 02/06/2021
1 ACCEPTED SOLUTION

Accepted Solutions
Garnet | Level 18

## Re: Calculate the number of new clients

Try next code -

``````proc sort data=have out=temp;
by ClientID Contact_date;
run;
data want;
set temp;
by ClientID;
retain first_contact;
if first.ClientID and last.ClientID then output;
else do;
if first.ClientID then first_contact = Contact_Date; else
if intnx('year',first_contact,5, 'same') < Contact_date
then output;
run;    ``````

What if somone makes a contact once a year for more then 5 years. Should he be considered as a new contact ? I think that the requirement since first service should be reconsidered.

3 REPLIES 3
Opal | Level 21

## Re: Calculate the number of new clients

Code like below should work.

``````data have;
infile datalines truncover;
input ClientID \$ ContactDate :ddmmyy10.;
format ContactDate date9.;
datalines;
A001 01/11/2010
A001 02/11/2010
A001 04/11/2010
B001 05/06/2010
B001 05/06/2016
B001 07/06/2016
B001 08/06/2016
C001 08/02/2017
D001 01/03/2014
A001 03/11/2021
A001 04/11/2021
E001 02/04/2021
G001
C001 02/06/2021
;

%let ref_p_start_dt=01Jan2021;
proc sql;
select
ClientID,
min(ContactDate) as ref_p_first_ContactDate format=date9.
from have o
where
ContactDate>="&ref_p_start_dt"d
and not exists
(
/* customers with contact within 5 years prior to reference period start date */
select *
from have i
where
intnx('year',"&ref_p_start_dt"d,-5,'s')< ContactDate < "&ref_p_start_dt"d
and o.clientID=i.ClientID
)
group by
ClientID
;
quit;``````

Garnet | Level 18

## Re: Calculate the number of new clients

Try next code -

``````proc sort data=have out=temp;
by ClientID Contact_date;
run;
data want;
set temp;
by ClientID;
retain first_contact;
if first.ClientID and last.ClientID then output;
else do;
if first.ClientID then first_contact = Contact_Date; else
if intnx('year',first_contact,5, 'same') < Contact_date
then output;
run;    ``````

What if somone makes a contact once a year for more then 5 years. Should he be considered as a new contact ? I think that the requirement since first service should be reconsidered.

Super User

## Re: Calculate the number of new clients

Your reference period is what exactly? I don't see a clear definition of any value for that.

Discussion stats
• 3 replies
• 333 views
• 3 likes
• 4 in conversation