Hi
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.
Do you want to merge by ID and LDMK?
Yes actually I merged by id and ldmk both but still there are issues.
1. Are those the only variables in the dataset?
2. Have you sorted both datasets?
3. Post your code/log
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.