Thank you in advance for your help Kurt! Here you go:
/* begin matching process */
data def.grp1 def.grp2;
set def.combined_finalab;
array x[4] var11 Financial_Fraud__Irregularities
Errors___Accounting_and_Clerical Other_Significant_Issues;
obs=_n_;
listCount1=0;
listCount2=0;
listCount3=0;
listCount4=0;
do i = 1 to 4;
if input(scan(var11,i,','),4.) GT .z then
listCount1=sum(listCount1,1);
if input(scan(Financial_Fraud__Irregularities,i,','),4.) GT .z then
listCount2=sum(listCount2,1);
if input(scan(Errors___Accounting_and_Clerical,i,','),4.) GT .z then
listCount3=sum(listCount3,1);
if input(scan(Other_Significant_Issues,i,','),4.) GT .z then
listCount4=sum(listCount4,1);
end;
listcount=sum(listcount1,listcount2,listcount3,listcount4);
if littler=0 then output def.grp1;
else output def.grp2;
drop i;
run;
/* Expand to 1 record per item in the list */
data def.grp1Tall;
set def.grp1;
do i = 1 to listCount1;
value=100+input(scan(var11,i,','),4.);
output;
end;
do i = 1 to listCount2;
value=200+input(scan(Financial_Fraud__Irregularities,i,','),4.);
output;
end;
do i = 1 to listCount3;
value=300+input(scan(Errors___Accounting_and_Clerical,i,','),4.);
output;
end;
do i = 1 to listCount4;
value=400+input(scan(Other_Significant_Issues,i,','),4.);
output;
end;
drop i;
run;
data def.grp2Tall;
set def.grp2;
do i = 1 to listCount1;
value=100+input(scan(var11,i,','),4.);
output;
end;
do i = 1 to listCount2;
value=200+input(scan(Financial_Fraud__Irregularities,i,','),4.);
output;
end;
do i = 1 to listCount3;
value=300+input(scan(Errors___Accounting_and_Clerical,i,','),4.);
output;
end;
do i = 1 to listCount4;
value=400+input(scan(Other_Significant_Issues,i,','),4.);
output;
end;
drop i;
run;
/* For each observation in grp1, find the number matching
in grp2. Only keep the records where the number of matches equals
the number in the grp1 list
*/
proc sql;
create table def.temp as
select a.listCount, a.obs, b.obs as obs2, count(*) as matchCount
from def.grp1Tall as a, def.grp2Tall as b
where a.value = b.value
and a.listCount LE b.listCount
group by 1,2,3
having listCount = matchCount;
quit;
/* Finally, join the two */
proc sql;
create table def.final as
select a.*, b.obs2, c.cik_code as cik_code2, c.var11 as var112, c. Financial_Fraud__Irregularities as Financial_Fraud__Irregularities2, c. Errors___Accounting_and_Clerical as E2, c. Other_Significant_Issues as Other_Significant_Issues2, c.lnassets as lnassets2, c.roa as roa2,
c.size as size2, c.mtb as mtb2, c.lev as lev2, c.fcf as fcf2, c.loss as loss2, c.foreign as foreign2, c.segment as segment2, c.abnbhar as abnbhar2, c.big4 as big42, c.city_expertise3 as city_expertise32, c.national_expertise3 as national_expertise32, c.icweakness as icweakness2, c.clientimp as clientimp2, c.contten as contten2, c.notbusy as notbusy2, c.littler as littler2, c.multiplerestatements as multiplerestatements2, c.sic as sic2, c.lnccse as lnccse2, c.effectx as effectx2, c.fyear as fyear2
from def.grp1 a, def.temp b, def.grp2 c
where a.obs=b.obs
and c.obs=b.obs2;
quit;
data def.final2;
set def.final;
if roa=. then delete;
if roa2=. then delete;
if lnassets=. then delete;
if lnassets2=. then delete;
distance=lnassets2-lnassets;
run;
proc sort data=def.final2;
by obs distance;
run;
data def.final3;
set def.final2;
by obs distance;
if first.obs;
run;
data def.final4;
set def.final3;
distance2=roa2-roa;
run;
proc sort data=def.final4;
by obs distance2;
run;
data def.final5;
set def.final4;
by obs distance2;
if first.obs;
run;
proc means data=def.final5;
run;
data def.bigr def.littler;
set def.final5;
if littler=0 then output def.bigr;
if littler2=1 then output def.littler;
run;
data def.bigr1;
set def.bigr;
keep cik_code fyear sic size mtb lev fcf roa loss foreign segment abnbhar big4 city_expertise3 national_expertise3 icweakness clientimp contten notbusy lnccse effectx var11 Financial_Fraud__Irregularities Errors___Accounting_and_Clerical Other_Significant_Issues littler multiplerestatements lnassets;
run;
data def.littler1;
set def.littler;
keep cik_code2 fyear2 sic2 size2 mtb2 lev2 fcf2 roa2 loss2 foreign2 segment2 abnbhar2 big42 city_expertise32 national_expertise32 icweakness2 clientimp2 contten2 notbusy2 lnccse2 effectx2 var112 Financial_Fraud__Irregularities2 E2 Other_Significant_Issues2 littler2 multiplerestatements2 lnassets2;
run;
data def.littler2;
set def.littler1;
rename cik_code2=cik_code;
rename fyear2=fyear;
rename sic2=sic;
rename size2=size;
rename mtb2=mtb;
rename lev2=lev;
rename fcf2=fcf;
rename roa2=roa;
rename loss2=loss;
rename foreign2=foreign;
rename segment2=segment;
rename abnbhar2=abnbhar;
rename big42=big4;
rename city_expertise32=city_expertise3;
rename national_expertise32=national_expertise3;
rename icweakness2=icweakness;
rename clientimp2=clientimp;
rename contten2=contten;
rename notbusy2=notbusy;
rename lnccse2=lnccse;
rename effectx2=effectx;
rename littler2=littler;
rename multiplerestatements2=multiplerestatements;
run;
proc sort data=def.bigr1 out=def.bigr2; by cik_code fyear;
run;
proc sort data=def.littler2 out=def.littler3; by cik_code fyear;
run;
data def.combinedfinal;
set def.bigr2 def.littler3;
run;
data def.combinedfinal1;
set def.combinedfinal;
run;
proc means data=def.combinedfinal1;
run;
title "Matching Results without SIC";
run;
proc logistic data= def.combinedfinal1;
class fyear SIC;
class Loss / param=ref ref=first;
class Foreign / param=ref ref=first;
class Big4 / param=ref ref=first;
class City_Expertise3 / param=ref ref=first;
class Natio6nal_Expertise3 / param=ref ref=first;
class ICWeakness / param=ref ref=first;
class Notbusy / param=ref ref=first;
class MultipleRestatements / param=ref ref=first;
model LittleR(event="1") = contten clientimp multiplerestatements
lnccse city_expertise3 national_expertise3 big4 size mtb segment
foreign lev fcf roa abnbhar icweakness notbusy loss fyear sic
/ link=probit RSQ;
output out= Def.combinedfinal1_prob3 p=Probability;
run;
Click image to register for webinar
