topic Re: Combining datasets in SAS Programming

Combining datasets

santose0 — Wed, 11 Sep 2019 20:06:26 GMT

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,

Re: Combining datasets

novinosrin — Wed, 11 Sep 2019 20:30:24 GMT


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;

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;

Re: Combining datasets

Jagadishkatam — Wed, 11 Sep 2019 20:29:01 GMT

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;

Re: Combining datasets

Tom — Wed, 11 Sep 2019 21:04:11 GMT

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;

Re: Combining datasets

santose0 — Thu, 12 Sep 2019 22:36:09 GMT

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