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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.