Help using Base SAS procedures

Comparing two tables

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

Comparing two tables

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           


Accepted Solutions
Solution
‎08-29-2013 04:46 PM
Super User
Posts: 5,084

Re: Comparing two tables

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.

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Comparing two tables

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

Super Contributor
Posts: 1,040

Re: Comparing two tables

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

Super Contributor
Posts: 307

Re: Comparing two tables

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;

Solution
‎08-29-2013 04:46 PM
Super User
Posts: 5,084

Re: Comparing two tables

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 253 views
  • 3 likes
  • 4 in conversation