topic Join two tables together based on similar but not equal columns in SAS in SAS Data Management

Join two tables together based on similar but not equal columns in SAS

AshJuri — Tue, 21 Apr 2020 14:26:12 GMT

data enroll;
    input patient status :$12. dateassigned &:anydtdte.;
    format date yymmdd10.;
    datalines;

500-001   enrolled    01-jan-2019      
500-002   enrolled    15-jan-2019     
500-003   removed     23-Jan-2019     
500-004   enrolled    05-feb-2019     
500-005   enrolled    17-feb-2019     
587-001   enrolled    20-feb-2019
587-002   enrolled    25-feb-2019
587-003   enrolled    03-mar-2019
594-001   enrolled    04-feb-2018
594-002   enrolled    09-feb-2018
648-001   enrolled    15-mar-2019
648-002   enrolled    22-mar-2019
648-003   enrolled    27-mar-2019
648-004   enrolled    30-mar-2019
;

data calendar;
    input visitnumber patientID :$12. datetracked &:anydtdte.;
    format date yymmdd10.;
    datalines;

500 500-001-rdf   01-jan-2019      
500 500-002-fgh   15-jan-2019     
500 500-003-ehd   23-Jan-2019     
500 500-004-ern   05-feb-2019     
500 500-005-qmd   17-feb-2019     
587 587-001-wcs   20-feb-2019
587 587-002-qlc   25-feb-2019
587 587-003-qhr   03-mar-2019
594 594-001-qwn   04-feb-2018
594 594-002-agj   09-feb-2018
648 648-001-wuf   15-mar-2019
648 648-002-qbf   22-mar-2019
648 648-003-olr   27-mar-2019
648 648-004-wmf   30-mar-2019
;

Hi all, I am trying to join these two these tables together. I want the columns to align where the column “patient” in table “enroll” matches the column “patientID” in table “calendar”. "patient" and "patientID" are similar but not equal. In addition, the column “dateassigned” in table “enroll” should match the column “datetracked” in table “calendar”. Since this is only a snippet of a larger dataset, the columns should align together. However, this may not be the case in the larger dataset, so I am trying to highlight where the columns from both tables do not align. Thank you in advance.

Re: Join two tables together based on similar but not equal columns in SAS

unison — Tue, 21 Apr 2020 14:44:59 GMT

This is how you can join these unequal fields. You may adjust to inner join, left join, etc as needed.

proc sql;
	create table want as
		select 
			enrl.*
			, cal.*
			from 
				enroll enrl
				, calendar cal
				where
					enrl.patient = substr(cal.patientID,1,7) and
					enrl.dateassigned = cal.datetracked
	;
quit;

Re: Join two tables together based on similar but not equal columns in SAS

ballardw — Tue, 21 Apr 2020 15:16:00 GMT

I might use

where index(cal.patientID,enrl.patient,)>0   and

in case the length of enrl.patient (and possibly cal.patientID) varies more than is shown in the example.

Re: Join two tables together based on similar but not equal columns in SAS

Jagadishkatam — Tue, 21 Apr 2020 15:52:11 GMT

Alternatively try the merge statement as well as below

proc sort data=enroll;
by patient dateassigned;
run;

data calendar;
length patient $8.;
set calendar;
patient=substr(patientID,1,7);
dateassigned=datetracked;
run;

proc sort data=calendar;
by patient dateassigned;
run;

data want;
merge enroll(in=a) calendar(in=b);
by patient dateassigned;
if a and b;
run;

Re: Join two tables together based on similar but not equal columns in SAS

AshJuri — Tue, 21 Apr 2020 17:04:49 GMT

Thank you for your reply. I am given the ERROR: Variable patient has been defined as both character and numeric.