proc report

[hein68]

I am trying to make a report that would look like this:

Top 25 diagnoses:	All	Age 0	Ages 1-17	Ages 18+
Most frequent diagnosis	560000	20000	270000	270000
Second most frequent diagnosis	500000
…..
25th most frequent diagnosis

This is the code I have so far:

proc report data=top25wvars nowd;
       column diag1ccs age_3cat;
       define diag1ccs / 'Top 25 CCS diagnoses' group style(header)={text_decoration=underline};
       define age_3cat/ across;
run;

What I haven't been able to figure out is how to get the "All" column into the table.  The "All" column would have the counts of the top 25 diagnoses (all, not broken out by age), in descending order with the most frequent diagnosis at the top to the least frequent diagnosis.  The column with "Top 25 CCS diagnoses" also needs to be in descending order from the most frequent diagnosis at the top to the least frequent diagnosis.

Thanks for your help.

[data_null__]

proc format; value $sex(notsorted multilabel) 'M','F'='All' 'M'='Male' 'F'='Female'; quit;
proc report data=sashelp.class list;
   columns age sex;
   define age / group;
   define sex / across f=$sex. preloadfmt mlf order=data;
   run;

 

[ballardw]

Or a different proc:

proc tabulate data=top25wvars nowd;
   class diag1ccs age_3cat;
   table diag1ccs=' ',
         all='All' age_3cat=' '
         /box='Top 25 CCS diagnoses' 
   ;
run;

[hein68]

This is great!

I just need to get rid of the row with the "N"s, and get the diagnoses in order starting from most frequent to least frequent (according to the all column).  Do you know how to do that?

[ballardw · Accepted solution]

---

@hein68 wrote:

This is great!

I just need to get rid of the row with the "N"s, and get the diagnoses in order starting from most frequent to least frequent (according to the all column).  Do you know how to do that?

---

You can suppress the statistic labels by explicitly listing the statistic and suppressing the label by:

proc tabulate data=top25wvars nowd;
   class diag1ccs age_3cat;
   table diag1ccs=' ',
         (all='All' age_3cat=' ') * n=' '
         /box='Top 25 CCS diagnoses' 
   ;
run;

Without data I am going to guess that perhaps specifying the Order=freq for the diaglccs variable will work. 

proc tabulate data=top25wvars nowd;
   class diag1ccs / order=freq ;
   class age_3cat;
   table diag1ccs=' ',
         all='All' age_3cat=' '
         /box='Top 25 CCS diagnoses' 
   ;
run;

[hein68]

Your code at the top worked for suppressing the N row 🙂

The part about getting the records in order resulted in an error message though.

The data is set up like this (see attachment).

[ballardw]

---

@hein68 wrote:

Your code at the top worked for suppressing the N row 🙂

The part about getting the records in order resulted in an error message though.

The data is set up like this (see attachment).

---

Show the actual code submitted and the error message. Run the code, go to the Log, copy code and message. In the forum open a code box using the forum's {I} icon and paste the text. You want to paste into a code box to preserve text formatting as the message windows here will reformat text and move things like indicators where an error was found.

BTW: I may misspell your variable names as I have no data to run the code against. So check that.

[hein68]

Actually your code does exactly what I needed.  It must have been my mistake the first time I tried it.

Thanks VERY much!