Full join - proc sql

[LanMin]

Thanks to both Ksharp and Urvish!

Jul 8, 2013 2:46 AM Urvish posting:

what if you have more variables, how should your code be changed?

e.g.

Data one;

input Cusip Year aa aa1 aa2;

cards;

100 97 10  3 2

101 98 2 2 3

102 97 . . .

103 98 2 . 3

;

Data two;

input Cusip Year bb bb1;

cards;

101 98 . 1

102 97 . 0

103 98 3 9

104 99 5 2

;

-Lan

[UrvishShah]

Hi,

Hope it works...In below macro no_of_vars parameter represents matching columns to be compare...like aa1,aa2,aa3, and bb1,bb2,bb3 and so on...so pass the value accordingly...

Here you only have 1 common column like aa1 and bb1....

%macro obs_identify(no_of_vars =);

    proc contents data = one noprint

                    out  = one_list(keep = name where = (name ^in ("Cusip" "Year")));

    run;

    proc contents data = two noprint

                    out  = two_list(keep = name where = (name ^in ("Cusip" "Year")));

    run;

      proc sql noprint;

         select name into :one_list separated by ","

         from both_list;

         select two_name into :two_list separated by ","

         from both_list;

      quit;

    proc sql;

        create table both as

        select coalesce(a.cusip,b.cusip) as cusip,

               coalesce(a.year,b.year) as year,

               &one_list.,&two_list.

               case

                   when a.cusip = b.cusip then "Match"

                   when aa = . and bb = . then "Match"

                           /* Loop for Series of Matching Columns */

                           %do i = 1 %to &no_of_vars.;

                       when bb&i. = . then "Two"

                       when aa&i. = . then "One"

                         %end;

                   else "Match"

               end as source

        from one as a

             full join

             two as b

        on a.cusip = b.cusip;

    quit;

%mend;

%obs_identify(no_of_vars = 1);

-Urvish

[UrvishShah]

Hi,

You can create the macro variable which contains all the columns from dataset one and dataset two...by this way you can modify 5th line of my code...

proc contents data = one noprint

              out  = one_name(keep = name where = (name ^in ("Cusip" "Year")));

run;

proc contents data = one noprint

              out  = one_name(keep = name where = (name ^in ("Cusip" "Year")));

run;

proc sql noprint;

   select name into :one_list separated by ","

   from one_name;

   select name into two_list separated by ","

   from two_list;

quit;

And after that just use the following line insted of 5th line...

&one_list., &two_list.,

-Urvish