one to many merge in SAS to complete lines

Question

I am struggling with merge in the statistics programm SAS and hope you guys can help me:

I have Dataset that I want to join together it looks smth. like this:

input:

 id |var1 |var2 |var3 |var4 |Var5
>--------------------------------<
 1  |A1   |B1   |C1   | --  | 0  | 
 1  |A2   |B2   |--   | D2  | 1  |

desired output:

 id |var1 |var2 |var3 |var4 |Var5
 >--------------------------------<
 1  |A1   |B1   |C1   |   D2|   0|
 1  |A2   |B2   |C1   |   D2|   1|

I tried to seperate the data set in two by if "Var5=0/1 then delete" statments and then merging them together like: Data example1

 id |var1 |var2 |var3 |var4 |Var5
>----------------------------<
1   |A1   |B1   |C1   | -- | 0|

Data Example2

 id |var1 |var2 |var3 |var4 |Var5

 >--------------------------------<

 1| A2    | B2  |--   |  D2|    1|

Merge code:

 data Example12;
 merge example1 (IN=X) example2;
  by persnr;
  IF x=1;
run;

but this results in something like:

 id |var1 |var2 |var3 |var4 |Var5

1|A1 |B1  |C1|     D2| 0|
1|A1 |B1  |C1|     D2| 0|

any help greatly appreciated.

This is a pure SAS question and although many of us here know SAS it is off topic for this site. Merging can be tricky. Migrate to StackOverflow. They are suppose to answer such questions. — Michael R. Chernick

Tom Tom · Accepted Answer · 2017-01-09T13:35:06

If it is the case that only one record per ID will have a non missing value for the VAR3 or VAR4 then you could use dataset options to set up a one-to-many merge that would get the value of VAR3 and VAR4 merged onto all rows for that ID.

First let's setup your example data:

data have ;
  input (id var1-var4) ($) var5 ;
cards;
1 A1 B1 C1  . 0
1 A2 B2  . D2 1
;

Now try the merge:

data want ;
  merge have(drop=var3 var4)
        have(keep=id var3 where=(not missing(var3)))
        have(keep=id var4 where=(not missing(var4)))
  ;
  by id;
run;

one to many merge in SAS to complete lines

1 Answers