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

WELL GROUPBY DID NOT WORK. THREW AN ERROR ... View more

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 prov_tin                1111                            1                    minor                          no                                1 prov_tin                1111                            1                    minor                          no                                1 national               1111                             1                    minor                         no                              31645 national               1111                             1                    minor                         no                              31645 national               1111                             1                    minor                         no                              31645 I am going to try also throwing in a groupby ... View more

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. ... View more

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? ... View more