Hello there,
I'm trying to count the number of visits by each client within the data step for the following data (input) and desired outcome (output):
data input;
input client $ visit $;
datalines;
1 .
2 .
2 Y
3 Y
4 .
5 .
5 Y
5 .
5 Y
5 .
6 Y
;
run;
data output;
input client $ visit $ count;
datalines;
1 . 0
2 . 0
2 Y 1
3 Y 1
4 . 0
5 . 0
5 Y 1
5 . 2
5 Y 3
5 . 4
6 Y 1
;
run;
Normally counting the number of visits by each client would be relatively simple task for me with the following code:
proc sort data=input;by client;
data output;
set input;
by client;
if first.client then count=1;
else count+1;
run;
But, one complication I would like to add is to start counting when there is a "Y" for the client. For instance Client 1 does not have a "Y" in the VISIT column so the count for him/her will start with 0 and end with 0. Client 2 has a "Y" in his/her second visit so the count will start from his/her first visit as 0 and second visit as 1. Client 5 does not have a "Y" in his/her first visit and has a "Y" in his/her second visit so the count will start from the second visit as 1. Even though his/her 3rd visit does not have a "Y" just because we started to count in the second visit we will continue to count until we see a new client.
I hope I was clear in my explanation and would like to thank in advance!
Regards,
Recep
Reduced to its core, you want to increment count once the first visit='Y' record has been encountered for a given id:
data output;
set input;
by client;
if first.client then count=0;
if count>0 or visit='Y' then count+1;
run;
data input;
input client $ visit $;
datalines;
1 .
2 .
2 Y
3 Y
4 .
5 .
5 Y
5 .
5 Y
5 .
6 Y
;
run;
data want;
if 0 then set input;
count=0;
do until(last.client);
set input;
by client;
if visit='Y' then do; _t=1;count+1;end;
else if _t=1 then count+1;
output;
end;
drop _t;
run;
Simple once you think of it:
data want;
set have;
by client;
if first.client then do;
count=0;
increment=0;
end;
retain increment;
if visit='Y' then increment=1;
count + increment;
drop increment;
run;
Reduced to its core, you want to increment count once the first visit='Y' record has been encountered for a given id:
data output;
set input;
by client;
if first.client then count=0;
if count>0 or visit='Y' then count+1;
run;
All three proposed solutions worked. This one is the easiest to understand though. Thanks a lot everyone for their help!
Recep
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.
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.