How can I adjust this working code so the observations in column "patient" from table "enroll" that don't match the observations in column "patientID" from table "calendar" are output into another table? In addition, would it be possible to output both matching observations and non-matching observations in the same table? The current working code only returns a table of matching observations based on "patient" and patientID", and "dateassigned" and "datetracked".
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
;
proc sql;
create table want as
select
enrl.*
, cal.*
from
enroll enrl
, calendar cal
where
enrl.patient = substr(cal.patientID,1,8) and
enrl.dateassigned = cal.datetracked
;
quit;
You cannot write to multiple tables at once with SQL.
You can use LEFT (or RIGHT) JOIN to keep unmatched observations from one of the inputs.
create table want as
select
enrl.*
, cal.*
from enroll enrl
left join calendar cal
on enrl.patient = substr(cal.patientID,1,8)
and enrl.dateassigned = cal.datetracked
;
You cannot write to multiple tables at once with SQL.
You can use LEFT (or RIGHT) JOIN to keep unmatched observations from one of the inputs.
create table want as
select
enrl.*
, cal.*
from enroll enrl
left join calendar cal
on enrl.patient = substr(cal.patientID,1,8)
and enrl.dateassigned = cal.datetracked
;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.