BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Recep
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20
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;
Astounding
PROC Star

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;

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Recep
Quartz | Level 8

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

 

Recep

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

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
  • 4 replies
  • 16550 views
  • 5 likes
  • 4 in conversation