topic Re: Counting in data step in SAS Programming

Counting in data step

Recep — Sat, 27 Jan 2018 01:05:30 GMT

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

Re: Counting in data step

novinosrin — Sat, 27 Jan 2018 01:22:32 GMT

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;

Re: Counting in data step

Astounding — Sat, 27 Jan 2018 02:27:19 GMT

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;

Re: Counting in data step

mkeintz — Sat, 27 Jan 2018 04:28:18 GMT

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;

Re: Counting in data step

Recep — Mon, 29 Jan 2018 21:25:33 GMT

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

Recep