Combining datasets

[santose0]

I've run through combinations of using the Modify, Update , Merge , and Set statements.   I'm sure this question has been answered several times over but I've not been able to find a match.   I have two datasets that contain the same variables but have missing data within several of the records.  My issue is trying to lineup the variables to get a match scenario.  I'm simply trying to create a report that shows which stated tasks are using which ports on two separate images.  

ex:   dataset A:  Production

                    Name            PPORT

                    BBMMVE      1225

                    BBMMVE      3940

        dataset B:  Disaster Recovery

                     Name            ZPORT

                     BBMMVE      3940

Based on these observations in trying to merge or combine these datasets. I haven't been able to obtain the desired outcome

                    Name:         PPORT    ZPORT

                    BBMMVE    1225           .

                    BBMMVE    3940         3940   

Using the Merge Statement , by Name gets :

                    Name:         PPORT    ZPORT

                    BBMMVE    1225         3940

                    BBMMVE    3940         3940

Using the Modify, SET and Update statements. --- I can never get the obs to match up.  

Is there a way to use the Merge Statement and not modify the missing variables ?    

Thank you.....

Regards,

Ed   

[novinosrin]

data a;
input  Name $  PPORT;
cards;
BBMMVE      1225
BBMMVE      3940
;

data b;
input  Name $  ZPORT;
cards;
BBMMVE      3940
;

proc sql;
create table want as
select a.*,zport
from a a left join b b
on a.pport=b.zport;
quit;

or 

proc sql;
create table want as
select a.*,zport
from a a left join b b
on a.pport=b.zport and a.name =b.name;
quit;

[Jagadishkatam]

Please try

proc sort data=a;
by name;
run;

proc sort data=b;
by name;
run;

data want;
merge a(in=i) b(in=j);
by name;
if a and b;
if pport^=zport then zport=.;
run;

[Tom]

Copy the two variables into one and then merge by both name and port number. 

data a2 ;
 set a ;
 port=pport;
run;
data b2 ;
 set b;
 port = zport;
run;

data want ;
  merge a2 b2 ;
  by name port;
run;

You could even do it via RENAME= dataset option.

data want;
  merge a(in=in1 rename=(pport=port)) b(in=in2 rename=(zport=port));
  by name port;
  if in1 then pport=port;
  if in2 then zport=port;
run;

Or do it with SQL

proc sql;
create table want as
 select coalesce(a.name,b.name) as name
      , coalesce(a.pport,b.zport) as port
      , a.pport
      , b.zport
 from a full join b on a.name=b.name and a.pport=b.zport 
 order by 1,2
;
quit;

[santose0]

Excellent !!!  I got the following to work:

proc sql;
create table want as
select coalesce(a.name,b.name) as name
      , coalesce(a.pport,b.zport) as port
      , a.pport
      , b.zport
from a full join b on a.name=b.name and a.pport=b.zport
order by 1,2
;
quit;

Now, one last step....  All observations are correct except for the following.

I have some observations that look like this:

OBS  NAME      PORT   PPORT  ZPORT 

 36    IZUSVR1  10446   10446         .

 37    IZUSVR1  15448       .           15448

What is the best way to merge these observations into one ?

         NAME         PPORT    ZPORT

         IZUSVR1    10446      15448 

Thank you for all your help !!!  Best Regards, Ed