topic Re: Output both observations in a merge if they match in SAS Programming

Output both observations in a merge if they match

Feefee — Fri, 09 Apr 2021 10:44:15 GMT

Hi Guys!

Quick question on merging and joining datasets:

If I have two datasets and if they both have a matching obs (by a certain key), how can i output both observations from each dataset into my output dataset please?

Re: Output both observations in a merge if they match

Kurt_Bremser — Fri, 09 Apr 2021 10:52:39 GMT

Do those datasets have the same structure (same variables)?

Re: Output both observations in a merge if they match

Feefee — Fri, 09 Apr 2021 10:53:13 GMT

Yep!

Re: Output both observations in a merge if they match

Kurt_Bremser — Fri, 09 Apr 2021 11:05:37 GMT

See these simple example tables:

data a;
input x y;
datalines;
1 2
2 3
3 4
;

data b;
input x y;
datalines;
1 3
2 4
4 6
;

You can either merge them and use a RENAME= option:

data want1;
merge
  a (in=a)
  b (in=b rename=(y=_y))
;
by x;
if a and b;
run;

Or you can stack them:

data want2;
set
  a (in=a)
  b (in=b)
;
by x;
if first.x ne last.x;
run;

Mind that this works only if there's maximally 1 observation for a key in each dataset.