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
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;
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
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
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
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.