BookmarkSubscribeRSS Feed
ZRick
Obsidian | Level 7

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


6 REPLIES 6
ZRick
Obsidian | Level 7

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
Fluorite | Level 6

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
Obsidian | Level 7

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
Quartz | Level 8

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
Fluorite | Level 6

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1053 views
  • 1 like
  • 6 in conversation