topic append / join the columns to the duplicate records, using proc sql, how? in SAS Programming

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

ZRick — Mon, 18 Feb 2013 23:16:21 GMT

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

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

ZRick — Tue, 19 Feb 2013 01:01:35 GMT

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;

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

allurai0412 — Tue, 19 Feb 2013 03:55:38 GMT

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

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

damanaulakh88 — Tue, 19 Feb 2013 11:15:53 GMT

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

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

kuridisanjeev — Tue, 19 Feb 2013 11:50:01 GMT

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

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

UrvishShah — Tue, 19 Feb 2013 17:01:57 GMT

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;

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

Tanmay — Wed, 06 Jan 2016 10:54:25 GMT

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.