topic Re: Give Three Most Frequent Results for Group Variable in PROC REPORT in SAS Programming

Give Three Most Frequent Results for Group Variable in PROC REPORT

tburus — Sun, 21 Jun 2020 19:04:12 GMT

I have a dataset containing whose rows are sports injuries, with each injury having a diagnoses and cause. I have cut this down to the three most frequently occurring diagnoses, and would now like to use PROC REPORT to summarize the three most frequent diagnoses along with the three most frequent causes for each.

Sample code:
PROC REPORT DATA=have;
COLUMN dx cause;
DEFINE dx / group;
DEFINE cause / computed;
COMPUTE cause ...
(not sure what to put here to get what I want)
RUN;

Is it possible to use a COMPUTE block to compute the three most frequent causes for each diagnosis and list them in order? I have hard coded a solution to this in a DATA step, but would like a programming solution that is more efficient. Thanks.

Re: Give Three Most Frequent Results for Group Variable in PROC REPORT

yabwon — Sun, 21 Jun 2020 20:27:37 GMT

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

Re: Give Three Most Frequent Results for Group Variable in PROC REPORT

yabwon — Sun, 21 Jun 2020 21:44:43 GMT

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

Re: Give Three Most Frequent Results for Group Variable in PROC REPORT

tburus — Sun, 21 Jun 2020 21:55:12 GMT

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?