topic Re: Different statistics for each variable in Proc Tabulate (SAS 9.4) in SAS Programming

Different statistics for each variable in Proc Tabulate (SAS 9.4)

Whitlea — Wed, 20 Mar 2024 22:52:22 GMT

I don't know if this is possible, but I would like different statistics for each variable in my proc tabulate.

I would like the following for each variable:

Gender- N for each level and colpctn

Age -min, max and median

Stays (Flag var)- sum

Days- sum (and average per ID for each Clinic Name if possible)

Cost-sum (and average per ID for each Clinic Name if possible)

ID- N and colpctn ( I do not want the IDs listed down the side of the table which is what the current code is doing now. see code below)

All of these for each clinic name with clinic name across the top which seems to be the only thing I have achieved so far in my code below.

I thought the proc tabulate would be the best option for this table as these statistics are not already calculated in the data, but I am open to suggestions!

Here is my code:

Proc tabulate data=Have;
format Cost dollar32.;
where Clinic_Type='1' and Flag=1 and Cost>1 and SFY_Yr='2023';
class Clinic_Name Gender Age Days Flag Cost ID;
table Gender Age Days Flag Cost ID all,
Clinic_Name*(n colpctn ) all / misstext=' ';
run;

Flag=1 flags a stay in clinic type 1 for clients, we are only looking at clinic type 1 (see where statement).

I'm looking to make something similar to this:

Thank you.

Re: Different statistics for each variable in Proc Tabulate (SAS 9.4)

ballardw — Wed, 20 Mar 2024 23:14:03 GMT

If you can provide some example data, or recast your example to use a SAS supplied data set like SASHELP.CLASS we can discuss details better.

Preferably a set small enough you can show expected results calculated by hand.

My first feeling without your data is that you are likely asking Tabulate to "cross statistics", this request a row with one statistic that will intersect with a calculated statistic in a column. That will generally not work. The likely problems I see are the "Days in Clinic" "Average Days per Stay" and the Min/Max/Average age crossing with the N and ColPctN . Your Tabulate code doesn't show anything related to Cost in Column position so I suspect more details are missing.

Here is an example of an invalid combination of statistics that requests a row N and intersects with a column mean. You can run this and see the error message.

Proc tabulate data=sashelp.class;
   class sex age;
   var height;
   table sex*n,
         age* height*mean
   ;
run;

With some example data it may be possible to preprocess the data to create a table like you envision but I suspect it will take a bit of work.

You can have different statistics in a column as long as the column only consists of CLASS variables. Or multiple stats in column as long as the Rows are all class variables.

Re: Different statistics for each variable in Proc Tabulate (SAS 9.4)

Whitlea — Thu, 21 Mar 2024 02:37:12 GMT

I think you are correct about the invalid combination of statistics. When I attempted to add the other statistics into the proc tabulate, I received the same error as the sample you provided.

Here is the sample data:

Data Have;
input 


Clinic_Name$ Gender$	Age	days	flag	Cost ID$	Clinic_Type	SFY_Yr$;
datalines;
A	F	16	157		1	2746 	A	1	2023
A	F	17	134		1	1373 	A	1	2023
B	M	17	169		1	5479 	B	1	2023
A	F	18	262		1	687 	C	1	2023
C	F	17	191		1	5479 	D	1	2023
D	F	17	20		1	8675 	E	1	2023
E	F	17	133		1	4566 	F	1	2023
E	F	17	60		1	10044 	F	1	2023
E	F	17	88		1	9588 	F	1	2023
A	F	17	271		1	2060 	G	1	2023
D	M	17	153		1	5022 	H	1	2023
D	M	16	118		1	913 	I	1	2023
F	M	16	328		1	457 	J	1	2023
G	F	17	179		1	10957 	K	1	2023
G	M	17	256		1	1826 	L	1	2023
H	F	17	163		1	10501 	M	1	2023
H	F	17	10		1	5022 	M	1	2023
B	M	17	105		1	7305 	N	1	2023
I	F	17	322		1	10501 	O	1	2023
A	F	17	314		1	1373 	P	1	2023

;
run;

Thank you for looking into this!

Re: Different statistics for each variable in Proc Tabulate (SAS 9.4)

Tom — Thu, 21 Mar 2024 02:42:14 GMT

Please show what report you want from that input.

There is a very nice utility macro out there for generating a report like you showed the row headers for in your original report.

https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-TABLEN/ta-p/634030

Why not just use that instead of trying do it in one PROOC TABULATE call?