topic Re: chi sq with expected in SAS Procedures

chi sq with expected

tmm — Tue, 08 May 2012 19:06:36 GMT

I am trying to get the expected also in my chi sq information and this is the code I am using.

PROC FREQ DATA = ReAdm.CHI2 ORDER = DATA; BY COMPARISON;

WEIGHT COUNT;

TABLES SVRTY_LVL_CD*WHO*ADMISSION/ CMH EXPECTED;

EXACT OR;

OUTPUT OUT = ReAdm.Final;

RUN;

When I view my final items I do not see expected anywhere in the columns. I can see the rest. How can I get expected in there as well?

Re: chi sq with expected

Reeza — Tue, 08 May 2012 19:12:58 GMT

Add outexpect to the table statement after the expected option and move the out option.

PROC FREQ DATA = ReAdm.CHI2 ORDER = DATA; BY COMPARISON;

WEIGHT COUNT;

TABLES SVRTY_LVL_CD*WHO*ADMISSION/OUT=READM.FINAL CMH EXPECTED OUTEXPECT;

EXACT OR;

RUN;

Re: chi sq with expected

tmm — Tue, 08 May 2012 20:40:23 GMT

The output was not what I needed. The code I have outputs all that is requested of me but they also want expected to be in that readm.final table. It is in the .rtf file. Will post that portion as well.

ODS RTF FILE='C:\documents and settings\tmill29\my documents\my sas files\9.2\readmin\readm.RTF';

PROC FREQ DATA = ReAdm.CHI2 ORDER = DATA; BY COMPARISON;

WEIGHT COUNT;

TABLES SVRTY_LVL_CD*WHO*ADMISSION/CMH EXPECTED OUTEXPECT;

EXACT OR;

OUTPUT OUT = ReAdm.Final OR CMH;

RUN;

ODS RTF CLOSE;

My output say for my first who which is a taxID has the expected for readmit of 0.1767 and expected for admit of 3.8233 in the rtf file created and then it has the other items associated with code above. When I look at that final for the output out all items for cmh and or are in there but the expected does not fee over to that. I tried putting expected outexpect in the output out = after the OR and CMH but that did not work.

Re: chi sq with expected

Reeza — Tue, 08 May 2012 21:05:08 GMT

I'm so not following what the issue is.

When I run code similar to yours I get no output.

proc sort data=sashelp.heart out=heart;

by status;

run;

proc freq data=heart order=data;

by status;

weight MRW;

table sex*bp_status*weight_status/CMH expected outexpect;;

exact OR;

output out=sample;

run;

If I run mine, I get the dataset with the expected in as well.

proc freq data=heart order=data;

by status;

weight MRW;

table sex*bp_status*weight_status/out=sample CMH expected outexpect;

exact OR;

run;

Re: chi sq with expected

tmcrouse — Wed, 09 May 2012 13:06:39 GMT

Maybe it is my version of SAS. I am on SAS 9.2 TS Level 2M3 XP_PRO Platform then it says Windows Version 5.1.2600

I have attached an example excel of the 2 different outputs. first tab using mine but there is no expected anywhere. 2nd is yours and the output is too limited. My output is what the executive team wants to see but they want to see it with expected somewhere in there. Also with yours it pulls that national stuff and they do not want that in there. This is why mine is done the way it is because it looks at the tax id only comparing the national numbers to the tax id numbers. All I need is what I have produced along with expected thrown in the mix somewhere. That is what the execs want to see.

Wow trying to attach an example is not easy. Here is what I get with your code: My output with my code I tried to attach so it would be easy to see but it is too big. So I will try my best to copy it below yours. I have far more rows with tins than what you see. Well I cannot attach or put my output. Too big for screen but when I export my final table I have from cell a to cell cj and i have chisq in mine and values derived from that; I get the CMH results; I get the mantel-haenszel results; I get Breslow. The executives want to see all that I have outputted but they want expected in there as well. Again mine also does not include the who with national. Mine outputs the tin only which is the comparison. I wished I could attach what mine does so you could see it.

COMPARISON	SVRTY_LVL_CD	WHO	ADMISSION	COUNT	EXPECTED	PERCENT
010473372	2	010473372	READMIT	1	0.176720721 0.002402402
010473372	2	010473372	ADMIT	3	3.823279279 0.007207207
010473372	2	NATIONAL	READMIT	1838	1838.823279 4.415615616
010473372	2	NATIONAL	ADMIT	39783	39782.17672 95.57477477
010717506	1	010717506	READMIT	1	0.16054635 0.002798925
010717506	1	010717506	ADMIT	5	5.83945365 0.013994626
010717506	1	NATIONAL	READMIT	955	955.8394536 2.672973578
010717506	1	NATIONAL	ADMIT	34767	34766.16055 97.31023287
020504866	2	020504866	READMIT	1	0.088364606 0.002402518
020504866	2	020504866	ADMIT	1	1.911635394 0.002402518
020504866	2	NATIONAL	READMIT	1838	1838.911635 4.415827788
020504866	2	NATIONAL	ADMIT	39783	39782.08836 95.57936718
030446814	1	030446814	READMIT	2	1.23076493 0.002582745
030446814	1	030446814	ADMIT	44	44.76923507 0.056820383
030446814	1	NATIONAL	READMIT	955	955.7692351 1.233260586
030446814	1	NATIONAL	ADMIT	34767	34766.23076 44.89714219
030446814	2	030446814	READMIT	2	2.119561305 0.002582745
030446814	2	030446814	ADMIT	46	45.8804387 0.059403128
030446814	2	NATIONAL	READMIT	1838	1837.880439 2.373542363
030446814	2	NATIONAL	ADMIT	39783	39783.11956 51.37466586
042461079	1	042461079	READMIT	1	0.080279916 0.00279916
042461079	1	042461079	ADMIT	2	2.919720084 0.005598321
042461079	1	NATIONAL	READMIT	955	955.9197201 2.673198041
042461079	1	NATIONAL	ADMIT	34767	34766.08028 97.31840448
042502803	1	042502803	READMIT	1	1.229478864 0.001291823
042502803	1	042502803	ADMIT	45	44.77052114 0.058132024

Re: chi sq with expected

Reeza — Wed, 09 May 2012 16:40:47 GMT

Well, I see an attachment and it includes a column called expected, Ie for 042502803 with admit the expected is 44.8 and your actual is 45 and the expected readmit is 1.23 and then actual is 1.

So is the expected you're looking for not the CHISQ EXPECTED Values?

Re: chi sq with expected

tmm — Wed, 09 May 2012 17:33:21 GMT

The above is based on your code and they do not want to see it like that. My code does not have any national stuff in the who. It uses the national numbers but only returns the TIN info but there is no expected returned when using my code. What the execs want is what is produced from my code but they also want an expected column in there. If I do your code to get expected I will have to manually clean the file and the file produces using your code 1864 rows that I would have to manually clean and then add to what mine outputs. I am not sure why there is not a way to do my code and have the output include the expected with it like it does from your code. If I have to manaully complete the puzzle I will. Just did not want to do that and not sure why SAS would not have thought of this. I mean it makes no sense to me as robust as everyone keeps telling me SAS is.

Re: chi sq with expected

Reeza — Wed, 09 May 2012 17:37:09 GMT

How would you need to manually clean it? Do you need it to look it does in the listing output.

SAS is robust and versatile but sometimes you do have to wrangle to get the output look like you want it to.

Re: chi sq with expected

tmm — Wed, 09 May 2012 17:48:49 GMT

I so wished I could attach what mine produces. Maybe I can just include 1 TIN and see if that is small enough for an Excel.

Well no dice. Even 1 TIN is still too large. So I took and split all the columns. What I am pasting here the columnsyou can see but they are all on row 1. I have a total of 38 columns produced with my code and again it all runs across 1 row. It is from column a all the way to column cj. I even added the expected outexpect to the code in hopes of getting what I produce with my code to include the expected in there.

COMPARISON	SVRTY_LVL_CD	N
010473372	2	41625
010473372		41625

_RROR_	L_RROR	U_RROR	XL_RROR	XU_RROR
7.214907508	0.750132724	69.3942401	0.13735972	89.89531756



_CMHCOR_	DF_CMHCO	P_CMHCOR	_CMHRMS_	DF_CMHRM

4.012935382	1	0.045152474	4.012935382	1


P_CMHRMS	_CMHGA_	DF_CMHGA	P_CMHGA	_MHOR_

0.045152474	4.012935382	1	0.045152474	7.214907508

L_MHOR	U_MHOR	_LGOR_	L_LGOR	U_LGOR

0.750132724	69.3942401	7.214907508	0.750132724	69.3942401



_MHRRC1_	L_MHRRC1	U_MHRRC1	_LGRRC1_	L_LGRRC1

5.661180631	1.036309133	30.92606746	5.661180631	1.036309133



U_LGRRC1	_MHRRC2_	L_MHRRC2	U_MHRRC2	_LGRRC2_

30.92606746	0.784650479	0.445608932	1.38165178	0.784650479




L_LGRRC2	U_LGRRC2	_BDCHI_	DF_BDCHI	P_BDCHI

0.445608932	1.38165178

PROC FREQ DATA = ReAdm.CHI2 ORDER = DATA; BY COMPARISON;

WEIGHT COUNT;

TABLES SVRTY_LVL_CD*WHO*ADMISSION/CMH EXPECTED OUTEXPECT;

EXACT OR;

OUTPUT OUT = ReAdm.Final OR CMH;

RUN;

My code above produces what I just pasted. You see there is no national nothing in there. I only want the numbers from national cuz that is the denominator. The TIN numbers are my numerator. The problem is how to incorporate my code output with your code output for expected for the TIN only. I guess if I cannot find the right SAS code to do this by the end of today I will just manually manipulate it in Excel because this project is due by end of day Friday and I am at a loss.

Re: chi sq with expected

Reeza — Wed, 09 May 2012 18:29:39 GMT

If you can explain what you'd want with respect to the Heart data it might be easier for me to understand. The heart data is default installed on your computer, under sashelp.heart, and I used it in an earlier example.

Then you can also post full data and the summaries of what's missing.

I'd suggest figuring out how to do it properly so next time it's easier and faster, but that's my perspective.

You can add attachments by clicking insert image as well, or at the bottom of the reply to attach an actual file.

Re: chi sq with expected

tmm — Wed, 09 May 2012 18:35:55 GMT

I need expected, or and cmh and I need it to output to that readm.final table so I can export it to Excel.

Re: chi sq with expected

Reeza — Wed, 09 May 2012 18:42:38 GMT

Do you already have something in the final table?

What is the layout you're expecting to see in that table, again with respect to the heart data.

Re: chi sq with expected

tmm — Wed, 09 May 2012 18:50:21 GMT

No. The data is in the CHI2 table and final is the output table.

Re: chi sq with expected

tmm — Wed, 09 May 2012 19:46:16 GMT

I am just going to do both codes, mine and yours and then do a proc sql to get the expected frequency under independence into the output table I produce with my code. That will save me some manual work via Excel because I can link on the TIN and just say <> national in the who section. I just don't see in sashelp.heart where there is anyway to do an odds ratio, cmh and expected and have it output odds ratio, cmh and expected information. Kind of strange that the SAS creators would not have a code that outputs expected along with or, cmh, chisq, all or anything else. I have tried all and expected does not show up using just all. I have tried all with expected outexpect and still I do not get any OR, CMH, CHISQ nothing. I get the same output with only 7 columns no matter what I do with an expected outexpect in it. I don't want that. I want the 38 or so columns I produce with OR and CMH but I also want the expected frequency under independence in there.

Re: chi sq with expected

Reeza — Wed, 09 May 2012 20:29:27 GMT

You can't have the OR and Expected and CMH and all in the same table because they all have different dimensions so to speak.

CHISQ is one number

OR is nlevels-1 for each variable

and the Expected is the # of rows * # of columns.

You can get each of what you want into an individual table using the ods features.

If you want to create a summary table with all that information, you need to write your own code to get those together. But if you can't provide a sample I'm only guessing and to be honest from what your asking it doesn't make any logical or statistical sense.

Re: chi sq with expected

tmm — Wed, 09 May 2012 20:51:53 GMT

I know it does not make a whole lot of sense but it is what they want. Because when I run the 2 separate and look at it you end up with 2 expecteds. 1 for the admission and one for the readmission. I will go back to them and see if we can outst the expected because it is just not going to work the way they think. Might need just 2 separate tabs so they can see expected. Don't know. Execs just want what they want and do not care about obstacles.

Re: chi sq with expected

Reeza — Wed, 09 May 2012 21:28:08 GMT

You mean by hospital/site they want to see the expected number of admits and readmits, ie the column or row total rather than the expected cell total?