SASの多くの変数の中から一致する日付とタイトルを見つける

あなたは行うことができますFULL JOINかLeft JOINと、使用Case一致するレコードを示すために、計算フィールドを作成するステートメントを。

以下のコードは、完全結合を実行し、フラグフィールドを作成します。

Table1とTable2の作成：1つのレコードのみが一致します

data table1;
input employeeID $ JobTitle1 $ MonthYearofTitle1 Jobtitle2 $ MonthYearofTitle2 ;
informat MonthYearofTitle1 monyy7. MonthYearofTitle2 monyy7.;
format MonthYearofTitle1 monyy7. MonthYearofTitle2 monyy7.;
datalines;
JohnDoe Intern Jan2016 Admin Jan2017
TomJones Junior Jul2016 Admin Jul2017
;
run;

data table2;
input employeeID $ JobTitle1 $ MonthYearofTitle1 ;
informat MonthYearofTitle1 monyy7.;
format MonthYearofTitle1 monyy7.;
datalines;
MarySue Admin Jan2017
JackieC Admin Jul2013
;
run;

完全参加：すべてのデータを取得するには

proc sql;
create table want as
select
t1.employeeID as t1_employeeID , t2.employeeID as t2_employeeID, 
t2.JobTitle1 as t2_JobTitle,
t2.MonthYearofTitle1 as t2_MonthYearofTitle1,
case when 
((t1.JobTitle1=t2.JobTitle1 and t1.MonthYearofTitle1=t2.MonthYearofTitle1) or (t1.JobTitle2=t2.JobTitle1 and t1.MonthYearofTitle2=t2.MonthYearofTitle1)) then "Match"
else "No-Match" end as flag
from table1 as t1 full join table2 as t2
on  (t1.JobTitle1=t2.JobTitle1 and t1.MonthYearofTitle1=t2.MonthYearofTitle1) or (t1.JobTitle2=t2.JobTitle1 and t1.MonthYearofTitle2=t2.MonthYearofTitle1)
;
quit; 

結果：

t1_employeeID=JohnDoe t2_employeeID=MarySue t2_JobTitle=Admin t2_MonthYearofTitle1=JAN2017 flag=Match
t1_employeeID=  t2_employeeID=JackieC t2_JobTitle=Admin t2_MonthYearofTitle1=JUL2013 flag=No-Match
t1_employeeID=TomJones t2_employeeID=  t2_JobTitle=  t2_MonthYearofTitle1=. flag=No-Match

更新：

左結合：テーブル1からレコードのみを取得するには

proc sql;
create table want as
select
t1.employeeID as Employee1    , t2.employeeID as Employee2, 
coalescec(t2.JobTitle1,t1.JobTitle2,t1.JobTitle1) as Title,
coalesce(t2.MonthYearofTitle1,t1.MonthYearofTitle2,t1.MonthYearofTitle1) as Date format monyy7.,
case when 
((t1.JobTitle1=t2.JobTitle1 and t1.MonthYearofTitle1=t2.MonthYearofTitle1) or (t1.JobTitle2=t2.JobTitle1 and t1.MonthYearofTitle2=t2.MonthYearofTitle1)) then "Match"
else "No-Match" end as Flag
from table1 as t1 left join table2 as t2
on  (t1.JobTitle1=t2.JobTitle1 and t1.MonthYearofTitle1=t2.MonthYearofTitle1) or (t1.JobTitle2=t2.JobTitle1 and t1.MonthYearofTitle2=t2.MonthYearofTitle1) 
;
quit;