topic How to get counts on different levels in New SAS User

How to get counts on different levels

JME1 — Tue, 03 Mar 2020 18:24:56 GMT

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;

Re: How to get counts on different levels

ballardw — Tue, 03 Mar 2020 23:24:18 GMT

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.

Re: How to get counts on different levels

ed_sas_member — Wed, 04 Mar 2020 11:59:55 GMT

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:

Re: How to get counts on different levels

JME1 — Wed, 04 Mar 2020 14:23:08 GMT

Thank you! This worked perfectly.

Re: How to get counts on different levels

JME1 — Wed, 04 Mar 2020 14:25:36 GMT

Thank you for responding @ballardw ! This helped me get the counts of procedure number done for each admission.