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.

Answer 1

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;