topic Re: How to merge matching observations but have different rows for non-matching observations? in SAS Programming

How to merge matching observations but have different rows for non-matching observations?

cdunlea — Wed, 12 Aug 2020 14:33:59 GMT

Hi all, I have the following two datasets I would like to merge.

ID_1	CODE
10059	11
10059	36
10077	7
10077	43
10233	29
10233	31
10437	32

ID_2	CODES
10059	38
10059	41
10077	21
10077	23
10077	25
10233	29
10437	32

When merging currently using the following code:

DATA Merged;
merge wave1 wave2 ; by id;
run;

It comes out looking like this:

	id	code	codes
1	10059	11	38

10059

10077

10233

Whereas this is what I would like it to look like. if the two "code" columns do not match I would like them to be in separate rows. Similar to stacking the data. However if they are MATCHING, I would like them to be merged in the same row (highlighted in red).

	id	code	codes
1	10059	11	.
2	10059	.	38

3	10059	36	.
4	10059	.	41

5	10077	7	.
6	10077	.	21

7	10077	43	.
8	10077	.	25

9	10077	43	.
10	10077	.	23

10233

12	10233	31	.
13	10233	.	29

10437

Any ideas would be appreciated!

Re: How to merge matching observations but have different rows for non-matching observations?

Shmuel — Wed, 12 Aug 2020 14:44:29 GMT

Try next code:

data both / view=both;
  set table1 table2;
run;
proc sort data=both out=want nodupkey;
  by id code codes;
run;

Re: How to merge matching observations but have different rows for non-matching observations?

cdunlea — Wed, 12 Aug 2020 14:51:36 GMT

Thanks for the reply, but this code results in the matching observations still being in separate rows.

The output looks good for those that do not match but it does not merge observations with the same value in each column (seen in red in original post).

Thanks again!

Re: How to merge matching observations but have different rows for non-matching observations?

Shmuel — Wed, 12 Aug 2020 15:32:29 GMT

Then you need add some steps to your code:

DATA Merged;
merge wave1 wave2 ; 
  by id;
run;
data want;
 set merged;
     retain _code; drop _code;
     if code=codes then output; 
     else do;
           _code = codes;
           _codes = .;
           output;
           codes = _code;
           code=.;
           output;
     end;
run;

Re: How to merge matching observations but have different rows for non-matching observations?

cdunlea — Wed, 12 Aug 2020 15:53:18 GMT

Thanks! That almost works!

The only thing is that I still want this "38" in the first row to be coded as missing and only found in row 2. (Or the "41 in row 3 to be coded as "." and only found in row 4.) How can I specify this?

	id	code	codes
1	10059	1	38

10059

Colleen

Re: How to merge matching observations but have different rows for non-matching observations?

Shmuel — Wed, 12 Aug 2020 16:09:58 GMT

There was a typo in my code. Code tested should be:

DATA Merged;
merge wave1 wave2 ;   by id;
run;
data want;
 set merged;
     retain _code; drop _code;
     if code=codes then output; 
     else do;
           _code = codes;
           codes = .;
           output;
           codes = _code;
           code=.;
           output;
     end;
run;

Re: How to merge matching observations but have different rows for non-matching observations?

cdunlea — Wed, 12 Aug 2020 16:29:42 GMT

This one almost works. However, it seems to be only working about half the time. It kept row 12, which should have been deleted. But in row 14, it did it perfectly. Perhaps it is the length of the observations in the "code" columns?

	id	code	codes
12	10233	.	242930

10233

242930

10437

3234

Re: How to merge matching observations but have different rows for non-matching observations?

Shmuel — Wed, 12 Aug 2020 16:51:28 GMT

What is the rule to delete a record?

Re: How to merge matching observations but have different rows for non-matching observations?

cdunlea — Wed, 12 Aug 2020 17:32:23 GMT

If the two "code" columns do not match I would like them to be in separate rows. This works.

If they are MATCHING codes, I would like them to be merged in the same row and the other row to be deleted (highlighted in red) so that it appears like row 14 here, just once.

	id	code	codes
12	10233	.	242930

10233

242930

10437

3234

Re: How to merge matching observations but have different rows for non-matching observations?

Shmuel — Wed, 12 Aug 2020 17:57:50 GMT

Add next steps to previous code:

data  id2del;
   set want;  /*output of previous step */
        if code=codes;
        keep ID;
run;
proc sql; 
      create table want_new
      as select * from want
      where code ne codes and
        id in (select ID from id2del);
quit;

Re: How to merge matching observations but have different rows for non-matching observations?

Ksharp — Thu, 13 Aug 2020 13:12:57 GMT

data a;
infile cards expandtabs;
input ID	CODE;
cards;
10059	11
10059	36
10077	7
10077	43
10233	29
10233	31
10437	32
;
 
data b;
infile cards expandtabs;
input ID	CODES;
cards;
10059	38
10059	41
10077	21
10077	23
10077	25
10233	29
10437	32
;

data temp;
 merge a b;
 by id ;
run;
data want;
 set temp;
 if code ne codes then do;
  temp=codes;
  call missing(codes);
  output;
  codes=temp;
  call missing(code);
  output;
 end;
 else output;
 drop temp;
run;