append / join the columns to the duplicate records, using proc sql, how?

[ZRick]

How do I append the columns to the duplicate rows?

data t;

input id port $;

cards;

001 b_12

001 p_92

;

data b;

input id inst $;

cards;

001 mthd

;

I want the final table has: id, port and inst,

In addition, in inst column, both rows are filled? something like:

001 b_12 mthd

001 p_92 mthd

[ZRick]

I have implementation using data merge and hash table, but don't know how to do it in proc sql, it is actually a left outer join

/*merge data step implementation*/

proc sort data=work.t  out=t;by id; run;

proc sort data=p out=p;      by id; run;

data fnl;

merge t(in=a) p(in=c);

by id;

if a;

run;

/*hash table implementation*/

data fnl_result(drop=rc);

if 0 then set p;

declare hash tmp(dataset:"p");

rc=tmp.defineKey("id");

rc=tmp.defineData("inst");

rc=tmp.defineDone();

do until(eof);

        set t end=eof;

        call missing(inst);

        rc=tmp.find();

        output;

end;

stop;

run;

[allurai0412]

hi,

if question is ...how to do in SQL ,,following works...

but please let me know is there any query like evaluating DUPLICATES??

proc sql ;

create table merge as select x.* ,y.* from t x left outer join b y on x.id=y.id;

run;

Regards

ALLU

[damanaulakh88]

Hi ,

Try this , it works fine:-

================================

data t;

input id port $;

cards;

001 b_12

001 p_92

;

data b;

input id inst $;

cards;

001 mthd

;

proc sql;

create table aa as select a.id ,a.port,b.inst from t a left join b on a.id=b.id;

quit;

proc print data=aa;

run;

==============================================

Output:-

============================================

                                                 Obs    id    port    inst

                                                  1      1    b_12    mthd

                                                  2      1    p_92    mthd

==============================================

/Daman

[kuridisanjeev]

Hi..

you have to do basic left join to get your desired output.

proc sql;

create table have  as select a.id,a.port,b.inst from t a left join b  b

on a.id=b.id;

quit;

Regards.

Sanjeev.K

[UrvishShah]

I think it very simple...

proc sql;

     select a.id,a.port,

            b.inst

     from t as a,

          b as b

     where a.id EQ b.id;

quit;

[Tanmay]

Try This : 

proc sql;

create table Table_1 as

select t.*,b.inst

from t

left join b

on a.id = b.id;

quit;

or you can also try with inner join, as per our required logic.