DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 133

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

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


Frequent Contributor
Posts: 133

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

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;

Frequent Contributor
Posts: 97

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

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

Frequent Contributor
Posts: 81

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

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

Super Contributor
Posts: 276

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

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

Regular Contributor
Posts: 195

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

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;

Occasional Contributor
Posts: 5

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

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.

Ask a Question
Discussion stats
  • 6 replies
  • 258 views
  • 1 like
  • 6 in conversation