topic Re: Proc Freq Grouping in SAS Programming

Proc Freq Grouping

shoeGirl37 — Mon, 18 Nov 2019 20:39:09 GMT

Hello,

I have a table that I need to find the frequencies of patient appointments based on group of diagnosis.

I have the table set up where it's showing the months and counts of the appointments and also grouped by diagnosis category, I just need to break it down by each department.

When I try to do BY dept or include dept in the matrix, it's giving me 153 tables with every dx category.

Not every dept has every category.

I just need to have the freq for each dept but only the diagnosis included in that dept.

Here is what I have:

proc freq data=cplr.patientICD9Analysis4;
format Appt_Date monyy7.;
table DeptID*Diagnosis_Category*Appt_Date / nocol nocum norow;
where Provider_Type IN ("Physician", "Resident", "Fellow");

run;

Any help would be appreciated

Re: Proc Freq Grouping

PaigeMiller — Mon, 18 Nov 2019 20:43:33 GMT

table DeptID*Diagnosis_Category*Appt_Date / list;

Re: Proc Freq Grouping

shoeGirl37 — Mon, 18 Nov 2019 20:48:24 GMT

that is good, but is there a way to have it more in a table format?

seems long....

Re: Proc Freq Grouping

PaigeMiller — Mon, 18 Nov 2019 20:53:41 GMT

"table format"? Can you give an example? Do you want it in a SAS data set?

Re: Proc Freq Grouping

shoeGirl37 — Mon, 18 Nov 2019 20:55:53 GMT

I think when proc freq outputs it's divided by dept, it shows me a proc freq output for each dept which is good, but it also outputs all the diagnosis categories.

Is there an output where each table output will only limit to the diagnosis categories part of that dept and not all of them?

Re: Proc Freq Grouping

shoeGirl37 — Mon, 18 Nov 2019 20:59:44 GMT

and I would like to output it to excel

Re: Proc Freq Grouping

PaigeMiller — Mon, 18 Nov 2019 21:02:45 GMT

Maybe this? This creates a SAS data set named _COUNTS_ as output.

proc freq data=cplr.patientICD9Analysis4;
format Appt_Date monyy7.;
by deptid;
table Diagnosis_Category*Appt_Date / noprint list out=_counts_;
where Provider_Type IN ("Physician", "Resident", "Fellow");
run;

Re: Proc Freq Grouping

ballardw — Mon, 18 Nov 2019 21:37:33 GMT

If you provide some example data we might get closer.

If you don't need cumulative counts or percentages then Proc tabulate might be what you are looking for.

Try this and see if one is closer to what you want: Proc tabulate can have multiple table statements . The comma indicates a dimension. So the first will have a row heading or department id and date with separate columns for diagnosis category.

The second table would be one "page", or table, per department with rows of date and columns of diagnosis, and the third is rows of diagnosis category and date with columns for department. Tabulate has a number of different percentages so without knowing which makes sense I'm not going to confuse that issue.

proc tabulate data= data=cplr.patientICD9Analysis4;
format Appt_Date monyy7.;
class DeptID Diagnosis_Category Appt_Date ;
where Provider_Type IN ("Physician", "Resident", "Fellow");

table DeptID*Appt_Date ,
      Diagnosis_Category * n;
/*or*/
table DeptID,
      Appt_Date ,
      Diagnosis_Category * n;
/*or */
table Diagnosis_Category*Appt_Date ,
      deptID * n;


run;

Re: Proc Freq Grouping

Watts — Mon, 18 Nov 2019 21:48:49 GMT

You can use the CROSSLIST format together with the NOSPARSE option in the TABLES statement (in PROC FREQ).

tables DeptID*Diagnosis_Category*Appt_Date / crosslist nosparse;

Re: Proc Freq Grouping

shoeGirl37 — Tue, 19 Nov 2019 20:31:09 GMT

So this works great! What if I want to add patientID as a grouping?

Re: Proc Freq Grouping

ballardw — Tue, 19 Nov 2019 21:37:02 GMT

@shoeGirl37 wrote:

So this works great! What if I want to add patientID as a grouping?

Depends on where you want it to appear and how a variable is to be used. That is partially why I mentioned which examples have which things appear in a row, column or page heading. Within any of the dimensions the code when read from left to right with nesting (the *) means that for rows (or page) that the variable values will appear in left to right order. If the dimension is a column dimension then the left most variable would be at the top of a group with the nested values below.

For simple counts I would suggest playing around with the variables in a smallish data set like SASHELP.Class or SASHELP.CARS and place the group-like variables in different orders with and without * for nesting and/or ( ) around groups of variables to see what happens. Or read the friendly manual. There are enough concepts involved that SAS publishes a book just on Proc Tabulate.

Also Proc Report is very flexible with different approaches to layout. There are tradeoffs in both procedures, Tabulate is probably nicer when you need to nest results in both rows and columns but you can't request statistics in both the row and column generally.

Proc Report allows more flexibility in using values from columns to calculate values in other columns to the right in a table.

Proc Tabulate needs to know how a variable will be used: Grouping usually means the variable goes on a Class statement, if the variable is be used in statistics other than N related, such as Mean, Max, Stddev then the variable needs to be on a VAR statement.