topic Re: Merging sas data sets in SAS Programming

Merging sas data sets

Dritan007 — Mon, 14 Dec 2020 14:27:12 GMT

Hello guys.
I need to merge these 2 sets to get the below result:

The result I want is in third table but I can not get it with merge statement?

Thank you.

Re: Merging sas data sets

hhinohar — Mon, 14 Dec 2020 14:39:53 GMT

Try hash.

Please send data not in a picture but as a sas code.

data one;
  infile datalines dlm="09"x;
	input x y;
datalines;
1	10
1	11
1	15
2	34
2	30
3	40
;
run;

data two;
  infile datalines dlm="09"x;
	input x z;
datalines;
1	100
2	150
3	300
;
run;

data three;
	if _N_=1 then do;
		if 0 then set one;
		dcl hash h(dataset:"one",multidata:"y");
		h.definekey("x");
		h.definedata("y");
		h.definedone();
	end;
	set two;
	do while(h.do_over()=0);
		output;
	end;
run;

Re: Merging sas data sets

Astounding — Mon, 14 Dec 2020 14:48:08 GMT

MERGE is exactly the right thing to do. You are probably forgetting to add a BY statement:

data want;
   merge top_dataset bottom_dataset;
   by x;
run;

Re: Merging sas data sets

ballardw — Mon, 14 Dec 2020 15:38:21 GMT

Merge works fine for me

data one;
	input x y;
datalines;
1	10
1	11
1	15
2	34
2	30
3	40
;
run;

data two;
	input x z;
datalines;
1	100
2	150
3	300
;
run;

data want;
  merge one two;
  by x;
run;

However, if your actual data has multiple records with the same By value in both data sets the log will say so and the merge is likely not what you want.

When you say something like "but I can not get it with merge statement" you should include the code you tried. Sometimes you may be close, sometimes using the wrong approach entirely. Making us guess what you may have tried is like pulling hen's teeth.

Best is to show us the code used by copying from your log the entire procedure or data step code will all the associated notes, warnings or other messages and then pasting into a text box opened on the forum with the </> icon to preserve formatting.