How to do merge Many to Many Merge

[SASuserlot]

I have the 'visits' dataset ( one record per visit per side) and the 'vitals' dataset (one record per 'cat' per visit per visdt). How can I merge both datasets to get the corresponding 'visitnumber' from the 'visits' dataset for the respective visits? I appreciate your help.

How I want to  look after the merge:

                                                            visitnumber

100 UNSC 03-12-2019 HR                999.01
100 UNSC 05-12-2019 HR                999.02
100 UNSC 03-12-2019 BP                 999.01
100 UNSC 05-12-2019 RR                 999.02
100 week1 01-01-2020 HR                   1

data visits;
input subject visitnumber visit : $5. svdt $10. ;
cards;
100 999.01 UNSC 03-12-2019
100 999.02 UNSC 05-12-2019
100 1 week1 01-01-2020
100 2 week2 02-01-2020
100 2.1 UNSC 02-01-2020
100 3 week3 03-01-2020
100 3.01 UNSC 04-01-2020
100 3.02 UNSC 05-01-2020
;
run;

data vitals;
input subject  visit : $5. visdt $10. cat $3.;
cards;
100  UNSC 03-12-2019 HR
100  UNSC 05-12-2019 HR
100  UNSC 03-12-2019 BP
100  UNSC 05-12-2019 RR
100  week1 01-01-2020 HR
100  week1 01-01-2020 BP
100  week1 01-01-2020 RR
100  week2 02-01-2020 HR
100  UNSC 02-01-2020 HR
100  week3 03-01-2020 HR
100  UNSC 04-01-2020 HR
100  UNSC 05-01-2020 BP
100  UNSC 04-01-2020 RR
100  UNSC 05-01-2020 TP

;
run;

[ballardw]

WHY are your svdt and visdt variables character? If they are dates they should be dates.

MERGE by values is going to want sort order and if  you have character dates in a "MM-DD-YYYY" (or "DD-MM-YYYY" order) they will not sort at all nicely, not to mention I can't tell which you even have from the range of values chosen.

This seems to work adding the values:

proc sort data=work.visits;
   by subject svdt visit;
run;

proc sort data=work.vitals;
   by subject visdt visit;
run;

data want;
   merge work.vitals 
         work.visits (rename=(svdt=visdt))
   ;
   by subject visdt visit;
run; 

If you want the output in a specific order, such as maintaining the order from the Vitals data set, add an order variable so you can resort to that order. Or make the Visit variable values sortable in the order you may need.

[Tom · Accepted solution]

Before you can program some logic you need to have some actual logic to program.

It looks like you want to attach the unique VISITNUMBER variable to each observation of the VITALS datasets.  How do you know which of the two unscheduled (I assume that is what UNSC means) visits is which?

To me it looks like you just want to use the DATE values to match them.  That will work much better if you store the date values as actual date values and not strings.

data visits;
  input subject visitnumber visit :$5. svdt :mmddyy. ;
  format svdt yymmdd10.;
cards;
100 999.01 UNSC 03-12-2019
100 999.02 UNSC 05-12-2019
100 1 week1 01-01-2020
100 2 week2 02-01-2020
100 2.1 UNSC 02-01-2020
100 3 week3 03-01-2020
100 3.01 UNSC 04-01-2020
100 3.02 UNSC 05-01-2020
;

data vitals;
  input subject  visit :$5. visdt :mmddyy. cat $3.;
  format visdt yymmdd10.;
cards;
100  UNSC 03-12-2019 HR
100  UNSC 05-12-2019 HR
100  UNSC 03-12-2019 BP
100  UNSC 05-12-2019 RR
100  week1 01-01-2020 HR
100  week1 01-01-2020 BP
100  week1 01-01-2020 RR
100  week2 02-01-2020 HR
100  UNSC 02-01-2020 HR
100  week3 03-01-2020 HR
100  UNSC 04-01-2020 HR
100  UNSC 05-01-2020 BP
100  UNSC 04-01-2020 RR
100  UNSC 05-01-2020 TP
;

proc sort data=vitals;
  by subject visit visdt;
run;
proc sort data=visits;
  by subject visit svdt;
run;

data want;
  merge vitals(in=in1) visits(in=in2 rename=(svdt=visdt));
  by subject visit visdt;
  if in1;
run;

Result

Obs    subject    visit         visdt    cat    visitnumber

  1      100      UNSC     2019-03-12    HR        999.01
  2      100      UNSC     2019-03-12    BP        999.01
  3      100      UNSC     2019-05-12    HR        999.02
  4      100      UNSC     2019-05-12    RR        999.02
  5      100      UNSC     2020-02-01    HR          2.10
  6      100      UNSC     2020-04-01    HR          3.01
  7      100      UNSC     2020-04-01    RR          3.01
  8      100      UNSC     2020-05-01    BP          3.02
  9      100      UNSC     2020-05-01    TP          3.02
 10      100      week1    2020-01-01    HR          1.00
 11      100      week1    2020-01-01    BP          1.00
 12      100      week1    2020-01-01    RR          1.00
 13      100      week2    2020-02-01    HR          2.00
 14      100      week3    2020-03-01    HR          3.00

[SASuserlot]

Thank you @Tom @ballardw, it helped; apologies for keeping the dates in character forgot to apply the format.