Hello,
I will like to know the number of visit per patient and group visit in to "1 visit", "2 visit" or "3 visit or more".
I am getting stuck with my code and will appreciate any help.
Here is a sample data;
data readin;
input ID 1-6 Name$ 7-14 visit MMDDYY10.;
format visit MMDDYY10.;
cards;
1 David 4/5/2001
1 David 7/4/2001
2 Sam 4/5/2004
2 Sam 5/4/2005
3 Bane 8/7/2004
3 Bane 9/2/2010
3 Bane 8/7/2003
4 Dane 2/3/2003
4 Dane 8/7/2005
5 Ken 8/7/2006
5 Ken 6/3/2006
6 Priya 7/2/2009
;
run;
For example if a patient have 1 visit then visit=1. in this case priya has 1 visit but david has 2 visit so visit=2 and so on.
I tried using first.id and last.id but i got stuck.
data want;
set have;
by id;
if first.id then visit = 1;
else visit + 1;
run;
proc format;
value visits
1 = "1 visit"
2 = "2 visits"
other = "3 or more visits"
;
run;
proc sql;
create table want as
select
id,
name,
count(*) as visit format=visits.
from readin
group by id, name
;
quit;
I suppose you want to do something like this:
data want;
do i = 1 by 1 until (last.id);
set have; by id;
end;
length v $16;
select (i);
when (1) v = "1 visit";
when (2) v = "2 visits";
otherwise v = "3 visits or more";
end;
drop i visit;
rename v = visit;
run;
proc format;
value visits
1 = "1 visit"
2 = "2 visits"
other = "3 or more visits"
;
run;
proc sql;
create table want as
select
id,
name,
count(*) as visit format=visits.
from readin
group by id, name
;
quit;
@Kurt_Bremser @PGStats Thank you. Although i agreed to the solution however, I mad a mistake in my data representation so when I tried @Kurt_Bremser code I got a different result. My mistake was from my ID. This is the right format from 1 - 12
data readin;
input ID 1-6 Name$ 7-14 visit MMDDYY10.;
format visit MMDDYY10.;
cards;
1 David 4/5/2001
2 David 7/4/2001
3 Sam 4/5/2004
4 Sam 5/4/2005
5 Bane 8/7/2004
6 Bane 9/2/2010
7 Bane 8/7/2003
8 Dane 2/3/2003
9 Dane 8/7/2005
10 Ken 8/7/2006
11 Ken 6/3/2006
12 Priya 7/2/2009
;
run;
Both codes can accomodate this change:
data wantPG;
do i = 1 by 1 until (last.name);
set readin; by name notsorted;
end;
length v $16;
select (i);
when (1) v = "1 visit";
when (2) v = "2 visits";
otherwise v = "3 visits or more";
end;
drop i visit;
rename v = visit;
run;
proc format;
value visits
1 = "1 visit"
2 = "2 visits"
other = "3 or more visits"
;
run;
proc sql;
create table wantKB as
select
max(id) as maxId,
name,
max(visit) as lastVisit format=mmddyy10.,
count(*) as visit format=visits.
from readin
group by name
;
quit;
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
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.
Ready to level-up your skills? Choose your own adventure.