topic Re: How to get duplicate values after merging in New SAS User

How to get duplicate values after merging

stp84 — Thu, 28 Jul 2022 01:28:11 GMT

Hi!

I am trying to merge 3 datasets so I divided them into two separate merges.

usubjid column1 column2

1 9 5

2 1 6

3 2 7

4 5 6

I have tried without in= options as well. But its not giving the output I need. Below is the output I need duplicates to print.

usubjid column1 column2

1 9 5

2 1 6

3 2 7

4 5 6

I am not getting any errors, however its also not giving the output I need.

Thank you so much in advance for your help!

here is my code:

proc sort data=data1;
by usubjid;
run;

proc sort data=data2;
by usubjid;
run;

proc sort data=data3;
by usubjid;
run;



data MergeD1; (98=obs)
merge data1(in=a) data2;
by usubjid;
if a ;
run;



data MergeD2; (98 obs)
merge MergeD1(in=a) data3;
by usubjid;
if a;
run;



proc print data=MergeD2;

run;

Re: How to get duplicate values after merging

ballardw — Thu, 28 Jul 2022 03:39:59 GMT

Really need to provide example input.

As it is we do not know which data set(s) contain any values for column1 and column2 and how they are associated with Usubjid.

Merge is quite often not the tool to use more than one of your data sets contain multiple values of a By group. So we need to know if that exists in your data.

Re: How to get duplicate values after merging

Adriaan_Gouws — Thu, 28 Jul 2022 12:31:52 GMT

Good day

You're able to fill the blanks by using the below approach.

/*Dummy data*/
data have;
	infile datalines missover;
	input Key column1 column2;
	datalines;
1 9 5
2 1 6
3 2 7
3
3
4 5 6
4
4
;
run;

/*Select all complete rows*/
proc sql;
	create table have_complete as
		select
			*
		from have 
			where column1 <> .;
quit;

/*Combine and fill blanks*/
proc sql;
	create table want as
		select
			a.Key
			,coalesce(a.column1,b.column1) as column1
			,coalesce(a.column2,b.column2) as column2
		from have as a
			left join have_complete as b
				on (a.Key=b.Key);
quit;

Re: How to get duplicate values after merging

Ksharp — Thu, 28 Jul 2022 12:38:04 GMT

data have;
infile cards truncover;
input usubjid  column1 column2;
cards;
1            9             5
2            1             6
3            2             7
3                       
3                        
4            5             6
4            
4
;

data want;
 update have(obs=0) have;
 by usubjid;
 output;
run;