BookmarkSubscribeRSS Feed
Ruhi
Obsidian | Level 7

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.

4 REPLIES 4
Reeza
Super User

Do you want to merge by ID and LDMK?

Ruhi
Obsidian | Level 7

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

Reeza
Super User

1. Are those the only variables in the dataset?

2. Have you sorted both datasets?

3. Post your code/log

Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 984 views
  • 0 likes
  • 3 in conversation