topic Re: Issue in match merging in SAS Data Management

Issue in match merging

Ruhi — Fri, 17 Jul 2015 20:42:34 GMT

I am trying to merge two data sets, one contains id's and examiner's measurement on each landmark (L1 to L5) on lumbar spine.

Another data set contains same id , landmark(L1 to L5) and hand(Left or Right) , and I am merging the two data sets after sorting by id and then by landmark.

	Obs	ID	ldmk	SIDE1
	141	AE50	L1	RIGHT
	142	AE50	L1	LEFT
	143	AE50	L2	RIGHT
	144	AE50	L2	LEFT
	145	AE50	L3	RIGHT
	146	AE50	L3	LEFT
	147	AE50	L4	RIGHT
	148	AE50	L4	LEFT
	149	AE50	L5	RIGHT
	150	AE50	L5	LEFT


Obs	id	ldmk	respMT
63	AE50	L1	-1
64	AE50	L2	1
65	AE50	L3	1
66	AE50	L4	1
67	AE50	L5	-1

This is the data set I am getting after merging
Obs	DominantHand	ID	ldmk	SIDE1	respMT( I am getting)	Actually what I want
151	RIGHT	AE50	L1	RIGHT	-1	-1
152	RIGHT	AE50	L2	LEFT	1	-1
153	RIGHT	AE50	L3	RIGHT	1	1
154	RIGHT	AE50	L4	LEFT	1	1
155	RIGHT	AE50	L5	RIGHT	-1	1
156	RIGHT	AE50	L3	LEFT	-1	1
157	RIGHT	AE50	L4	RIGHT	-1	1
158	RIGHT	AE50	L4	LEFT	-1	1
159	RIGHT	AE50	L5	RIGHT	-1	-1
160	RIGHT	AE50	L5	LEFT	-1	-1

PROC SORT DATA=PEAKPRESSURE1 OUT=PEAKPRESSURE2;

BY ID LDMK;

RUN;

data merged_respmt;

merge peakpressure2 efinding;

by id ;run;

I want the resultant data set in which each id and landmark combination is matched to the response for each id and landmark combinations in examiner finding data set. I don;t know what I am doing wrong here , but results are not what I want.

Data sets are attached in the excel sheet

Your help is greatly appreciated.

Re: Issue in match merging

Reeza — Fri, 17 Jul 2015 21:27:29 GMT

Do you want to merge by ID and LDMK?

Re: Issue in match merging

Ruhi — Fri, 17 Jul 2015 21:45:57 GMT

Yes actually I merged by id and ldmk both but still there are issues.

Re: Issue in match merging

Reeza — Sat, 18 Jul 2015 01:54:33 GMT

1. Are those the only variables in the dataset?

2. Have you sorted both datasets?

3. Post your code/log

Re: Issue in match merging

Patrick — Sat, 18 Jul 2015 02:06:10 GMT

If you really merge by id AND ldmk the you'll get the expected result - at least with the sample data you've posted. Run the code below and you'll see.

data peakpressure;

infile datalines truncover dlm=' ';

input (ID ldmk SIDE1) ($);

datalines;

AE50 L1 RIGHT

AE50 L1 LEFT

AE50 L2 RIGHT

AE50 L2 LEFT

AE50 L3 RIGHT

AE50 L3 LEFT

AE50 L4 RIGHT

AE50 L4 LEFT

AE50 L5 RIGHT

AE50 L5 LEFT

;

run;

data efinding;

infile datalines truncover dlm=' ';

input id $ ldmk $ respMT :best32.;

datalines;

AE50 L1 -1

AE50 L2 1

AE50 L3 1

AE50 L4 1

AE50 L5 -1

;

run;

proc sort data=peakpressure out=peakpressure2;

by id ldmk;

run;

data merged_respmt;

merge peakpressure2 efinding;

by id ldmk;

run;