SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Issue in match merging

Reply
Contributor
Posts: 39

Issue in match merging

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.

ObsIDldmkSIDE1
141AE50L1RIGHT
142AE50L1LEFT
143AE50L2RIGHT
144AE50L2LEFT
145AE50L3RIGHT
146AE50L3LEFT
147AE50L4RIGHT
148AE50L4LEFT
149AE50L5RIGHT
150AE50L5LEFT
ObsidldmkrespMT
63AE50L1-1
64AE50L21
65AE50L31
66AE50L41
67AE50L5-1
This is the data set I am getting after merging
ObsDominantHandIDldmkSIDE1respMT( I am getting)Actually what I want
151RIGHTAE50L1RIGHT-1-1
152RIGHTAE50L2LEFT1-1
153RIGHTAE50L3RIGHT11
154RIGHTAE50L4LEFT11
155RIGHTAE50L5RIGHT-11
156RIGHTAE50L3LEFT-11
157RIGHTAE50L4RIGHT-11
158RIGHTAE50L4LEFT-11
159RIGHTAE50L5RIGHT-1-1
160RIGHTAE50L5LEFT-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.

Super User
Posts: 19,860

Re: Issue in match merging

Do you want to merge by ID and LDMK?

Contributor
Posts: 39

Re: Issue in match merging

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

Super User
Posts: 19,860

Re: Issue in match merging

1. Are those the only variables in the dataset?

2. Have you sorted both datasets?

3. Post your code/log

Respected Advisor
Posts: 4,173

Re: Issue in match merging

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;

Ask a Question
Discussion stats
  • 4 replies
  • 370 views
  • 0 likes
  • 3 in conversation