topic Merge with multiple variables in SAS Enterprise Guide

Merge with multiple variables

kfbaker0206 — Wed, 20 Nov 2019 20:22:28 GMT

Ok Here is Plan A dataset

PLAN	REQUIREMENT	LINE#	CDATE
A	RED	1	C024
B	YELLOW	2	C025
C	BLUE	3	C026
D	GREEN	4	C027

Ok Here is Plan B dataset

PLAN	REQUIREMENT	LINE#	CDATE
A	RED	1	C024
B	YELLOW	2	C026
C	BLUE	3	C026
D	GREEN	4	C028

When I merge the datasets together, I only want it to populate the lines that have changed. This is the end result that I want

PLAN	REQUIREMENT	LINE#	CDATE
B	YELLOW	2	C026
D	GREEN	4	C028

Can someone please assist? Thank you.

Re: Merge with multiple variables

r_behata — Wed, 20 Nov 2019 20:28:40 GMT

data plana;
input PLAN	$ REQUIREMENT $	LINE CDATE $;
cards;
A RED 1 C024
B YELLOW 2 C025
C BLUE 3 C026
D GREEN 4 C027
;
run;

data planb;
input PLAN	$ REQUIREMENT $	LINE CDATE $;
cards;
A RED 1 C024
B YELLOW 2 C026
C BLUE 3 C026
D GREEN 4 C028
;
run;

Proc SQl;
Create table want as 
select * from plana
except
select * from planb;
Quit;

Re: Merge with multiple variables

ed_sas_member — Wed, 20 Nov 2019 20:30:29 GMT


Data want;
Merge plan_A (in=x) plan_B (in=y);
By plan requirement line cdate;
If x=0 and y=1;
Run;

NB : your datasets need to be sorted by plan requirement line cdate

Re: Merge with multiple variables

PeterClemmensen — Wed, 20 Nov 2019 20:31:19 GMT

data a;
input PLAN $ REQUIREMENT $ LINE CDATE $;
datalines;
A RED    1 C024
B YELLOW 2 C025
C BLUE   3 C026
D GREEN  4 C027
;

data b;
input PLAN $ REQUIREMENT $ LINE CDATE $;
datalines;
A RED     1 C024
B YELLOW  2 C026
C BLUE    3 C026
D GREEN   4 C028
;


data want(drop=_: rc);
   if _N_=1 then do;
      declare hash h(dataset:'a(rename=cdate=_cdate');
      h.definekey('plan', 'requirement', 'line');
      h.definedata('_cdate');
      h.definedone();
   end;

   set b;
   length _cdate $ 8;

   rc=h.find();

   if cdate ne _cdate;
run;

Result:

PLAN REQUIREMENT LINE CDATE 
B    YELLOW      2    C026 
D    GREEN       4    C028

Re: Merge with multiple variables

Tom — Wed, 20 Nov 2019 20:31:42 GMT

What is your definition of changed? Do you just want to pick the observations that are in PLANB and not in PLANA?

proc sql;
create table want as
  select * from planb
  except 
  select * from plana
;
quit;

Re: Merge with multiple variables

ballardw — Wed, 20 Nov 2019 20:34:13 GMT

@kfbaker0206 wrote:

Ok Here is Plan A dataset

PLAN REQUIREMENT LINE# CDATE

A RED 1 C024

B YELLOW 2 C025

C BLUE 3 C026

D GREEN 4 C027

Ok Here is Plan B dataset

PLAN REQUIREMENT LINE# CDATE

A RED 1 C024

B YELLOW 2 C026

C BLUE 3 C026

D GREEN 4 C028

When I merge the datasets together, I only want it to populate the lines that have changed. This is the end result that I want

PLAN REQUIREMENT LINE# CDATE

B YELLOW 2 C026

D GREEN 4 C028

Can someone please assist? Thank you.

Merge in SAS usually means to combine data sets by rows in some fashion. Please be aware of that as you can get very odd suggestions if there isn't any actual input data and desired shared by misuse of "merge".

Since your requirement does not combine the data then that is not typically a merge.

Re: Merge with multiple variables

kfbaker0206 — Tue, 26 Nov 2019 12:07:36 GMT

Thank you!

Re: Merge with multiple variables

kfbaker0206 — Tue, 26 Nov 2019 12:08:37 GMT

Thank you.