topic Re: How to write the code if I would like to get the same result by using 'merge' function? in SAS Programming

How to write the code if I would like to get the same result by using 'merge' function?

Alexxxxxxx — Thu, 22 Oct 2020 07:07:00 GMT

Hi,

I am running code like below,

PROC SQL;
	CREATE TABLE want.C  AS
	SELECT DISTINCT
	*
	FROM
	have.A as A
	JOIN have.B AS B ON A.family_id=B.family_id
	;
QUIT;

my question is: How to write the code if I would like to get the same result by using 'merge' function?

I try to use

data want.C;
	merge have.A 
	have.B;
	by family_id;
run;

But I did not get the same result. could you please give me some suggestions about this?

Thanks in advance.

Re: How to write the code if I would like to get the same result by using 'merge' function?

PeterClemmensen — Thu, 22 Oct 2020 07:20:21 GMT

Try this

data want.C;
	merge have.A (in=a)
	have.B (in=b);
	by family_id;
	if a and b;
run;

Re: How to write the code if I would like to get the same result by using 'merge' function?

Kurt_Bremser — Thu, 22 Oct 2020 07:54:55 GMT

The data step MERGE cannot mimic the SQL join if there is a many-to-many relationship.

Re: How to write the code if I would like to get the same result by using 'merge' function?

Alexxxxxxx — Thu, 22 Oct 2020 07:57:01 GMT

Thanks for your reply. but what should I do? the result of SQL is 'Error: Sort execution failure.' Could you please give me some suggestions about this?

Re: How to write the code if I would like to get the same result by using 'merge' function?

Kurt_Bremser — Thu, 22 Oct 2020 08:02:14 GMT

@Alexxxxxxx wrote:
Thanks for your reply. but what should I do? the result of SQL is 'Error: Sort execution failure.' Could you please give me some suggestions about this?

See your other thread about this.

Re: How to write the code if I would like to get the same result by using 'merge' function?

Ksharp — Thu, 22 Oct 2020 12:30:31 GMT

Try FULL JOIN .
Better post sample data ,and can test the code .