Comparing two tables

[robertrao]

Hi team,

i have two tables and basically want to compare between them??

Table1

code    Descrip         category

45     head                    primary

45     head                 secondary

48     neck                  secondary

48      neck                  secondary

Table2

code      Descrip                     category

45      head                          primary

45     head                                 primary

45      head                           secondary

48     neck                          primary

48     neck                        secondary

48     neck                      secondary

48     neck                     secondary

48      neck                 secondary

WANT

code     Descrip                  primary fromTable1    primary from table2  secondary from table1   secondary from table2

45        head                             1                                    2                                       1                                   1

48        neck                               0                                    1                                         2                                   4           

[ballardw]

It really isn't obvious what you are comparing or how you want to handle multiple Code and Descrip instances.

[robertrao]

Basically I want a comparision the particular codes in the Primary category from table1 with the same code in the primary category from Table 2

When they are listed side by side we would know which table is overcoded or which is undercoded for a particular code

Bottomline is that if everything is done in the proper way both the tables should have equal number of primary /secondary for a particular code

Thanks

[Fugue]

Or if PROC SQL is your flavour (this is the 2nd post - accidentally submitted the first post and then deleted it):

proc sql ;

create table want as

select cd.*

  , coalesce ( q1.primarytable1, 0 ) as primarytable1

  , coalesce ( q2.primarytable2, 0 ) as primarytable2

  , coalesce ( q1.secondarytable1, 0 ) as secondarytable1

  , coalesce ( q2.secondarytable2, 0 ) as secondarytable2

from

/* create a set of unique codes from both datasets */

( select distinct coalesce ( t1.code , t2.code) as code, coalesce ( t1.descrip, t2.descrip ) as descrip

from table1 t1

full outer join

( select code, descrip

from table2 ) t2

on t1.code = t2.code ) cd

/* add up categories from table1 */

left join

( select code

  , sum ( case when category='primary' then 1 else 0 end ) as primarytable1

  , sum ( case when category='secondary' then 1 else 0 end ) as secondarytable1

from table1

group by code ) q1

on q1.code = cd.code

/* add up categoris from table2 */

left join

( select code

  , sum ( case when category='primary' then 1 else 0 end ) as primarytable2

  , sum ( case when category='secondary' then 1 else 0 end ) as secondarytable2

from table2

group by code ) q2

on q2.code = cd.code

order by code

;

quit;

[Astounding · Accepted solution]

Here's one way:

proc freq data=table1 noprint;

  tables code * descrip * category / out=table1_counts (drop=percent);

run;

proc freq data=table2 noprint;

  tables code * descrip * category / out=table2_counts (drop=percent);

run;

data want;

  merge table1_counts (where=(category='primary') rename=(count=primary_from_table_1))

            table1_counts (where=(category='secondary') rename=(count=secondary_from_table_1))

            table2_counts (where=(category='primary') rename=(count=primary_from_table_2))

            table2_counts (where=(category='secondary') rename=(count=secondary_from_table_2));

  by code descrip;

run;

It will generate missing values, rather than 0 but that's something you can easily interpret.

Good luck.