DATA Step, Macro, Functions and more

Counting in data step

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Counting in data step

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

 


Accepted Solutions
Solution
‎01-29-2018 04:23 PM
Trusted Advisor
Posts: 1,309

Re: Counting in data step

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;

View solution in original post


All Replies
PROC Star
Posts: 1,558

Re: Counting in data step

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;
Super User
Posts: 6,628

Re: Counting in data step

[ Edited ]

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;

Solution
‎01-29-2018 04:23 PM
Trusted Advisor
Posts: 1,309

Re: Counting in data step

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;
Contributor
Posts: 33

Re: Counting in data step

All three proposed solutions worked. This one is the easiest to understand though. Thanks a lot everyone for their help!

 

Recep

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 207 views
  • 5 likes
  • 4 in conversation