Hello! I need help with getting counts of how many procedures were done during each admission visit for each. Different patients have different number of admission visits and procedures completed during each visit.
- The number of procedures completed is counted by the amount of times the same admission number is repeated for each enrolid.
My data looks like this;
data test;
input id admission;
datalines;
1 121
1 121
1 121
1 141
1 141
2 111
;
run;
I want an output that shows for each id, how many distinct admission each one had (which I was able to get using code below). But I want for each distinct admission numbers, how many procedures were done at (visit1 - visitn)
This is what I want my output to look like
Output;
id freq visit1 visit2 visit 3;
1 2 3 2
2 1 1
;
run;
I was able to get the freq column by using the code below and just using a proc sql to get the max count by each enrolid, but I'm struggling with getting the frequency of the procedures done for each admission. I'll appreciate any help.
data x;
set test;
by id;
if first.id then count=0;
count+1;
run;
Hi @JME1
Here is an approach to achieve this:
data test;
input id admission;
datalines;
1 121
1 121
1 121
1 141
1 141
2 111
;
run;
proc sql;
create table test_freq as
select id, count(distinct admission) as freq
from test
group by id;
quit;
proc freq data=test noprint;
table admission / out=test_visit (drop=percent);
by id;
run;
proc transpose data=test_visit out=test_tr (drop=_:) prefix=visit;
var count;
by id;
run;
data want;
merge test_freq test_tr;
by id;
run;
Output:
Does this give enough to go forward:
data need; set have; by notsorted id admission; retain procedurenum; if first.admission then procedurenum=1; else procedurenum+1; if last.admission; run;
I don't know if your ID and/or admissions are actually sorted so used the NOTSORTED option.
Hi @JME1
Here is an approach to achieve this:
data test;
input id admission;
datalines;
1 121
1 121
1 121
1 141
1 141
2 111
;
run;
proc sql;
create table test_freq as
select id, count(distinct admission) as freq
from test
group by id;
quit;
proc freq data=test noprint;
table admission / out=test_visit (drop=percent);
by id;
run;
proc transpose data=test_visit out=test_tr (drop=_:) prefix=visit;
var count;
by id;
run;
data want;
merge test_freq test_tr;
by id;
run;
Output:
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.