BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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?

16 REPLIES 16
Reeza
Super User

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;

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

Reeza
Super User

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;

tmcrouse
Calcite | Level 5

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

  1. 0.176720721
  2. 0.002402402

010473372

2

010473372

ADMIT

3

  1. 3.823279279
  2. 0.007207207

010473372

2

NATIONAL

READMIT

1838

  1. 1838.823279
  2. 4.415615616

010473372

2

NATIONAL

ADMIT

39783

  1. 39782.17672
  2. 95.57477477

010717506

1

010717506

READMIT

1

  1. 0.16054635
  2. 0.002798925

010717506

1

010717506

ADMIT

5

  1. 5.83945365
  2. 0.013994626

010717506

1

NATIONAL

READMIT

955

  1. 955.8394536
  2. 2.672973578

010717506

1

NATIONAL

ADMIT

34767

  1. 34766.16055
  2. 97.31023287

020504866

2

020504866

READMIT

1

  1. 0.088364606
  2. 0.002402518

020504866

2

020504866

ADMIT

1

  1. 1.911635394
  2. 0.002402518

020504866

2

NATIONAL

READMIT

1838

  1. 1838.911635
  2. 4.415827788

020504866

2

NATIONAL

ADMIT

39783

  1. 39782.08836
  2. 95.57936718

030446814

1

030446814

READMIT

2

  1. 1.23076493
  2. 0.002582745

030446814

1

030446814

ADMIT

44

  1. 44.76923507
  2. 0.056820383

030446814

1

NATIONAL

READMIT

955

  1. 955.7692351
  2. 1.233260586

030446814

1

NATIONAL

ADMIT

34767

  1. 34766.23076
  2. 44.89714219

030446814

2

030446814

READMIT

2

  1. 2.119561305
  2. 0.002582745

030446814

2

030446814

ADMIT

46

  1. 45.8804387
  2. 0.059403128

030446814

2

NATIONAL

READMIT

1838

  1. 1837.880439
  2. 2.373542363

030446814

2

NATIONAL

ADMIT

39783

  1. 39783.11956
  2. 51.37466586

042461079

1

042461079

READMIT

1

  1. 0.080279916
  2. 0.00279916

042461079

1

042461079

ADMIT

2

  1. 2.919720084
  2. 0.005598321

042461079

1

NATIONAL

READMIT

955

  1. 955.9197201
  2. 2.673198041

042461079

1

NATIONAL

ADMIT

34767

  1. 34766.08028
  2. 97.31840448

042502803

1

042502803

READMIT

1

  1. 1.229478864
  2. 0.001291823

042502803

1

042502803

ADMIT

45

  1. 44.77052114
  2. 0.058132024
Reeza
Super User

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?

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6


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.

Reeza
Super User

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

COMPARISONSVRTY_LVL_CDN
010473372241625
010473372 41625
_RROR_L_RRORU_RRORXL_RRORXU_RROR
7.2149075080.75013272469.39424010.1373597289.89531756
_CMHCOR_DF_CMHCOP_CMHCOR_CMHRMS_DF_CMHRM
4.01293538210.0451524744.0129353821
P_CMHRMS_CMHGA_DF_CMHGAP_CMHGA_MHOR_
0.0451524744.01293538210.0451524747.214907508
L_MHORU_MHOR_LGOR_L_LGORU_LGOR
0.75013272469.39424017.2149075080.75013272469.3942401
_MHRRC1_L_MHRRC1U_MHRRC1_LGRRC1_L_LGRRC1
5.6611806311.03630913330.926067465.6611806311.036309133
U_LGRRC1_MHRRC2_L_MHRRC2U_MHRRC2_LGRRC2_
30.926067460.7846504790.4456089321.381651780.784650479
L_LGRRC2U_LGRRC2_BDCHI_DF_BDCHIP_BDCHI
0.4456089321.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.

Reeza
Super User

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

Reeza
Super User

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1301 views
  • 0 likes
  • 3 in conversation