topic chisq issue in SAS Procedures

chisq issue

tmm — Tue, 20 Mar 2012 21:12:22 GMT

Everything I have seen on trying to do chisq in SAS looks like this

who scd sdesc admission count

1111 1 major noreadm 200

1111 1 major yesredm 2

1111 2 extreme noreadm 100

1111 2 extreme yesreadm 15

national 1 major noreadm 500

national 1 major yesreadm 10

national 2 extreme noreadm 250

national 2 extreme yesreadm 25

2222 1 major noreadm 300

2222 1 major yesreadm 8

2222 2 extreme noreadm 150

2222 2 extreme yesreadm 10

I have 87k rows all together and after every number in the who column there is another national series with the same counts.

Then code is run and you get separate chi data based on the above.

Now I have 2 tables. 1 that has multiple rows based on the who number which are TIN numbers by the way. That is in my CHI table. Then I have a CHI3 table that took the national and pieced it out and then I summed it to get a table that is named CHI3. CHI has 87k rows with all the TINS that apply. CHI3 is just 4 rows total and I appended it to the CHI tables but now it does not look like the other chi above where the national appears after every list of TINS. I tried to run the CHIsq code off what I appended but it does not work. Is there just a way I can run the chisq by doing a join to the 2 tables? I hope I am not talking in circles and confusing everyone. I can offer more info if need be.

chisq issue

art297 — Tue, 20 Mar 2012 22:45:03 GMT

I, for one, am totally confused. I don't know what code you are running, on which data, or what results you expect to achieve.

chisq issue

PGStats — Wed, 21 Mar 2012 02:13:03 GMT

What are you trying to test with the ChiSq?

chisq issue

tmm — Wed, 21 Mar 2012 16:16:12 GMT

Let me try it this way. I have a table called CHI. The columns are who, svcd, svdesc, admission, count. The data in the who is a taxid and each taxid is logged 8 times because the svcd is 1-4. The svdesc is n/a, minor, major, extreme. the admission is no or yes and the count is the count of no or yes

The above table has a total of 21349 taxids represented.

Then I have table CHI2 which is the national representation of the above table. That is simply taking all the data and doing a sum of the count and then groupby the other stuff. The columns are the same however it is laid out like this:

who svcd svdesc admission count

national 1 n/a no 2990

national 1 n/a yes 5

national 2 minor no 31645

national 2 minor yes 52

national 3 major no 41864

national 3 major yes 154

national 4 extreme no 7589

national 4 extreme yes 19

I need to do a CHIsq off this data and all examples I see the first 8 rows of the data is the taxid then followed by the above 8 rows. then next 8 rows are next taxid followed again by the above 8 rows and so on. Then the chisq code is run to get chi results for each tin and each svcd and admission.

The problem is this means I would need to export both the CHI and CHI2 tables and manually add this above national data after every tax id series. That seems like far too much work. Is there a way maybe I can do an inner join on the 2 tables and tell it with code to do chisq for me?

chisq issue

Reeza — Wed, 21 Mar 2012 16:36:52 GMT

What would your hypothesis be?

Are you looking for each who to match the national or are you testing for differences between the who's?

What variables are you to test for differences between?

chisq issue

PGStats — Wed, 21 Mar 2012 16:37:52 GMT

Seems like you are looking for something like :

proc sql;
create table CHI3 as
select * from
(select "taxId" as source, who, svcd, svdesc, admission, count from CHI)
union all
(select "National", C.who, N.svcd, N.svdesc, N.admission, N.count from
CHI as C inner Join CHI2 as N
on C.svcd=N.svcd and C.svdesc=N.svdesc and C.admission=N.admission)
order by who, source, svcd, admission;

proc freq data=CHI3;
by who;
table ....;
run;

This will generate a lot of output...

chisq issue

tmm — Wed, 21 Mar 2012 16:49:30 GMT

Say the taxid with svcd is 2 which is minor and they have a count of 25 no admissions and then 2 yes admissions. The chisq would be of the national with the 2 as the svcd and the minor and as above national for that is 31645 no admissions and 52 yes admissions. So if I remember my chisq correctly 25/31645 and 2/52 shows the ratio. And using the chisq for cochran mantel with those no and yes counts i would get this as the result:

case control value = 6205.0000 95% confidence limit = 290.6214

col1 risk value = 5651.0714 95% confidence limit = 277.5314

cohort value = 0.9259 95% confidence limit = 0.8322

odds ratio is 0.0007900142 for no with svcd 2 and 0.0384615385 for yes with svcd 2

I manually did this based on my first group of TINS and the national data and manually did an input of the data and then ran the chisq and ratio code. But I am not about to manually input the national data and insert it after every 8 rows in the CHI main table that houses all the taxids. That would take forever. So all I am hoping for is to some how inner join the 2 tables and then run my chisq and ratio code and it will know that the first 8 rows of Taxid data compare to the national data and give me the results. Now take the next 8 rows of taxid data and do the same until all taxids are calculated. Otherwise the only thing I can see is exporting my 2 tables to Excel and doing the chisq and ratios in Excel but that is not what the boss wants. Wants it done in SAS but I do not know how unless I export both files to Excel and manually add the national data after every 8 taxid rows.

chisq issue

Reeza — Wed, 21 Mar 2012 17:04:55 GMT

Shouldn't your odds ratio be the inverse of each other?

Ie if no =0.0007900142 then for yes it is 1/0.0007900142 = 1265.8

chisq issue

PGStats — Wed, 21 Mar 2012 17:49:37 GMT

Given your latest explanations, I think we might be getting closer with:

proc freq data=CHI3 noprint;
by who svcd;
tables source*admission / chisq;
freq count;
output out=CHI_OUT chisq;
run;

chisq issue

tmm — Wed, 21 Mar 2012 18:03:11 GMT

The above code is very close but it is duplicating

The output if I just do not even do the proc freq and do a run instead of the quit so I get the table chi3 shows these results:

source who svcd svdesc admission count

prov_tin 1111 1 minor no 1

national 1111 1 minor no 31645

I am going to try also throwing in a groupby

chisq issue

tmm — Wed, 21 Mar 2012 18:04:08 GMT

WELL GROUPBY DID NOT WORK. THREW AN ERROR

chisq issue

tmm — Wed, 21 Mar 2012 18:08:46 GMT

added distinct to the 2 select clauses in the () and that got rid of the dupes.

chisq issue

tmm — Tue, 27 Mar 2012 19:53:57 GMT

Well this almost works but it makes the CHI process wrong. What I need is for the output of the code to be

who svcd svdesc admission count

national 1 minor no 1295

national 1 minor yes 152

1111 1 minor no 12

1111 1 minor yes 3

national 1 minor no 1295

national 1 minor yes 152

2222 1 minor no 24

2222 1 minor yes 5

With the above code it outputs like this:

source who svcd svdesc admission count

national 1111 1 minor no 1295

national 1111 1 minor yes 152

provtin 1111 1 minor no 12

provtin 1111 1 minor yes 3

national 2222 1 minor no 1295

national 2222 1 minor yes 152

provtin 2222 1 minor no 24

provtin 2222 1 minor yes 5

By doing the chi it does not work on my source*admission because it adds everything together. I need the output to be correct so that my chi works

out right

chisq issue

PGStats — Tue, 27 Mar 2012 21:27:58 GMT

Then replace the query by :

proc sql;

create table CHI3 as

select * from

(select who, svcd, svdesc, admission, count from CHI)

union all

(select "National", N.svcd, N.svdesc, N.admission, N.count from

CHI as C inner Join CHI2 as N

on C.svcd=N.svcd and C.svdesc=N.svdesc and C.admission=N.admission)

order by who, svcd, admission;

chisq issue

tmm — Wed, 28 Mar 2012 12:15:46 GMT

I did that but then national only appears one time and the chi square calculation does not work. The chi examples of input i have seen have been like this and remember this is simply an example and does not include everything mine does. This is an input example from something someone else did and then they did the chi and it output tons of results

who admission count

1111 no 30

1111 yes 3

ntl no 32652

ntl yes 1480

2222 no 112

2222 yes 6

ntl no 32652

ntl yes 1480

3333 no 50

3333 yes 2

ntl no 32652

ntl yes 1480

they have the national results below every taxid and then when doing the chi it calculates say for 1111 with count of 30 no's against count of ntl 32652 no's and then count of 3 yes's against the count of the ntl 1480 yes's. That is what I need mine to do but I have svcd and svdesc in mine as well. so mine needs to chi not only against the no and yes of national but break down by svcd and svdesc.

chisq issue

tmm — Wed, 28 Mar 2012 12:17:47 GMT

so now I am thinking I should just export the table and then manually do stuff in excel to make it look like the example I was provided and import to SAS but that I am sure that is not what past programmers in my company have done. I am sure they figured out a way to do it all in SAS and my boss has even said I should do this all in SAS but I am left to figure it out on my own and as we can see by my number of posts I am not able too. Have read tons of books and just spinning my wheels and wasting time. I have my meeting Friday to show my data but I am no where near able to do that unless I figure this out.

chisq issue

tmm — Wed, 28 Mar 2012 15:06:29 GMT

I think I got it. I played and played and this is the code I used:

proc sql;

create table chi4 as

select * from

(select distinct who as source, who, svcd, svdesc, admission, count from chi)

union all

(select distinct "national", a.who, b.svcd, b.svdesc, b.admission, b.count from

chi as a inner join chi3 as b

on a.svcd = b.svcd and

a.svdesc = b.svdesc and

a.admission = b.admission)

order by who, svcd, source, admission);

run;

The output chi4 table looks like this:

who who svcd svdesc admission count

1111 1111 1 minor no 1

1111 1111 1 minor yes 0

national 1111 1 minor no 31645

national 1111 1 minor yes 52

2222 2222 2 moderate no 1

2222 2222 2 moderate yes 0

national 2222 2 moderate no 41864

national 2222 2 moderate yes 154

etc

Now I have to do a step to drop the 2nd who column or rename the label or something so when I do my chi it looks at the who with the numbers and national and then the remaining columns. Because if I use the 2nd column then it will add all the counts together. If I use first column it will chi it correctly from what I can tell.

chisq issue

tmm — Wed, 28 Mar 2012 15:23:31 GMT

I added a data step because even though the labels in the chi4 table both show who, the actual column name is source and who so I did this step:

data chi5 (drop = who);

set chi4;

run;

It drops the 2nd who column and leaves the other who column which is actually source and looks the way it should. So let me see how my chi works now.

Re: chisq issue

mlamias — Thu, 11 Oct 2012 15:37:50 GMT

Why on earth would you want to perform a Chi-Squared Test on a table with this many cells?

Re: chisq issue

SteveDenham — Fri, 12 Oct 2012 12:01:39 GMT

And with so many cells with virtually no representation (counts<2)? A sensible aggregation seems in order here.

Steve Denham