topic Re: Comparing two tables in SAS Procedures

Comparing two tables

robertrao — Thu, 29 Aug 2013 18:40:58 GMT

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

Table2

code Descrip category

45 head primary

45 head secondary

48 neck primary

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

Re: Comparing two tables

ballardw — Thu, 29 Aug 2013 19:25:49 GMT

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

Re: Comparing two tables

robertrao — Thu, 29 Aug 2013 19:34:28 GMT

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

Re: Comparing two tables

Astounding — Thu, 29 Aug 2013 20:46:46 GMT

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.

Re: Comparing two tables

Fugue — Fri, 30 Aug 2013 00:16:28 GMT

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;