Hi,
Maybe SQL + datastep could be a solution:
data have;
do injury = "A", "B", "C";
do diagnose = "1.0","2.0","3.0","4.0","5.0";
do _N_ = 1 to input(diagnose, best32.);
output;
end;
end;
end;
run;
proc sql;
create table tmp as
select injury, diagnose, count(diagnose) as cnt
from have
group by injury, diagnose
order by injury, cnt desc
;
quit;
data want;
do _N_ = 1 by 1 until(last.injury);
set tmp;
by injury;
if _N_ <=3 then output;
end;
run;
proc print;
run;
All the best
Bart
Hi,
sorry for my previous response, I didn't notice that you wanted to have "top 3" diagnoses with "top 3" causes, SQL + data step adjusted:
data have;
do injury = "A", "B", "C";
do d = "1.0","2.0","3.0","4.0","5.0"; drop d;
diagnose = "diagnose" || d;
do _N_ = 1 to input(d, best32.);
do c = "1.0","2.0","3.0","4.0","5.0"; drop c;
cause = "cause" || c;
do _iorc_ = 1 to input(c, best32.)*10 + _N_;
output;
end;
end;
end;
end;
end;
run;
proc sql;
create table tmp as
select d.injury, d.diagnose, d.cause, d.cnt_diag, c.cnt_cs
from
(
select distinct injury, diagnose, cause, count(diagnose) as cnt_diag
from have
group by injury, diagnose
) as d
join
(
select distinct injury, diagnose, cause, count(cause) as cnt_cs
from have
group by injury, diagnose, cause
) as c
on d.injury = c.injury and
d.diagnose = c.diagnose and
d.cause = c.cause
order by injury, d.cnt_diag desc, c.cnt_cs desc, d.diagnose, d.cause
;
quit;
data want;
do _N_ = 1 by 1 until(last.injury);
set tmp;
by injury diagnose cause notsorted;
sum_of_d + last.diagnose;
sum_of_c + last.cause;
if sum_of_d < 3 and sum_of_c <= 3 then output;
if last.diagnose then sum_of_c = 0;
end;
sum_of_d = 0;
drop sum_of:;
run;
proc print;
run;
All the best
Bart
Thank you. I'm comfortable with getting what I want from PROC SQL. What I'm curious about is if there is a way to generate the same results using a COMPUTE step in a PROC REPORT?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.