Matching Strings States of Geographic Operation with States Affected by Disaster

Agent1592 — Fri, 24 Apr 2020 00:04:23 GMT

Dear SAS Community:

I have two tables. The first one has firm identifier, fiscal year, and states of geographic operations separated by a dash. The second table has year, quarter and the state name that was affected by some kind of disaster. I am trying to created an indicator variable equal to one if the firm in have1 operates (geogt_disper) in a state affected by natural disaster from have2.

I am looking for a code something like:

proc sql;
	create table want as 
		select a.*, b.dummy
		from have1 a left join have2 b on year(a.fyr)=b.year and qtr(a.fyr)=b.qtr and  findw(a.geogr_disper,b.state,'-')>0;
quit;

data WORK.have1;
  infile datalines dsd truncover;
  input CIK:$10. FYR:MMDDYY8. geogr_disper:$200.;
  format FYR MMDDYY8.;
datalines4;
0000000020,12/31/02,PA-NJ-DE-
0000000020,09/30/02,PA-NJ-DE-
0000000020,06/30/02,PA-NJ-DE-
0000000020,03/31/02,PA-NJ-DE-
0000000020,03/31/03,SC-PA-OH-NJ-DE-
0000000020,12/31/03,SC-PA-OH-NJ-DE-
0000000020,06/30/03,SC-PA-OH-NJ-DE-
0000000020,09/30/03,SC-PA-OH-NJ-DE-
0000000020,06/30/04,SC-PA-OH-NJ-DE-
0000000020,03/31/04,SC-PA-OH-NJ-DE-
0000000020,09/30/04,SC-PA-OH-NJ-DE-
0000000020,12/31/04,SC-PA-OH-NJ-DE-
0000000020,09/30/05,VA-SC-PA-OH-NJ-DE-
0000000020,06/30/05,VA-SC-PA-OH-NJ-DE-
0000000020,03/31/05,VA-SC-PA-OH-NJ-DE-
0000000020,12/31/05,VA-SC-PA-OH-NJ-DE-
0000000020,03/31/06,VA-SC-PA-OH-NJ-IL-DE-
0000000020,09/30/06,VA-SC-PA-OH-NJ-IL-DE-
0000000020,12/31/06,VA-SC-PA-OH-NJ-IL-DE-
0000000020,06/30/06,VA-SC-PA-OH-NJ-IL-DE-
0000000020,09/30/07,SC-PA-OH-NJ-KS-IL-
0000000020,06/30/07,SC-PA-OH-NJ-KS-IL-
0000000020,12/31/07,SC-PA-OH-NJ-KS-IL-
0000000020,03/31/07,SC-PA-OH-NJ-KS-IL-
0000000020,06/30/08,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,12/31/08,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,09/30/08,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,03/31/08,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,03/31/09,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,12/31/09,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,09/30/09,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000000020,06/30/09,VA-SC-PA-OH-NJ-KS-IL-GA-DE-
0000001750,05/31/02,OK-NY-NM-MI-IL-FL-DE-CT-
0000001750,08/31/02,OK-NY-NM-MI-IL-FL-DE-CT-
0000001750,02/28/02,OK-NY-NM-MI-IL-FL-DE-CT-
0000001750,11/30/02,OK-NY-NM-MI-IL-FL-DE-CT-
0000001750,02/28/03,OK-NY-NM-MI-IL-GA-FL-DE-CT-
0000001750,08/31/03,OK-NY-NM-MI-IL-GA-FL-DE-CT-
0000001750,05/31/03,OK-NY-NM-MI-IL-GA-FL-DE-CT-
0000001750,11/30/03,OK-NY-NM-MI-IL-GA-FL-DE-CT-
0000001750,05/31/04,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,08/31/04,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,11/30/04,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,02/29/04,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,02/28/05,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,05/31/05,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,08/31/05,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,11/30/05,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,05/31/06,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,08/31/06,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,02/28/06,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,11/30/06,OK-NY-NM-MI-IN-IL-GA-FL-DE-CT-
0000001750,05/31/07,OK-NC-NY-NM-MI-IN-IL-GA-FL-DE-CT-AR-AL-
0000001750,08/31/07,OK-NC-NY-NM-MI-IN-IL-GA-FL-DE-CT-AR-AL-
0000001750,11/30/07,OK-NC-NY-NM-MI-IN-IL-GA-FL-DE-CT-AR-AL-
0000001750,02/28/07,OK-NC-NY-NM-MI-IN-IL-GA-FL-DE-CT-AR-AL-
0000001750,02/29/08,OK-NC-NY-NM-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,11/30/08,OK-NC-NY-NM-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,08/31/08,OK-NC-NY-NM-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,05/31/08,OK-NC-NY-NM-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,02/28/09,OK-NC-NY-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,08/31/09,OK-NC-NY-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,11/30/09,OK-NC-NY-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,05/31/09,OK-NC-NY-MI-KY-IN-IL-GA-FL-DE-CT-CA-AR-AL-
0000001750,02/28/17,OK-NC-NY-MN-MI-LA-IN-IL-FL-DE-CA-AL-
0000001750,08/31/17,OK-NC-NY-MN-MI-LA-IN-IL-FL-DE-CA-AL-
0000001750,05/31/17,OK-NC-NY-MN-MI-LA-IN-IL-FL-DE-CA-AL-
0000001750,11/30/17,OK-NC-NY-MN-MI-LA-IN-IL-FL-DE-CA-AL-
0000001750,05/31/18,OK-NC-NY-MN-MI-IN-IL-FL-DE-CA-AL-
0000001750,11/30/18,OK-NC-NY-MN-MI-IN-IL-FL-DE-CA-AL-
0000001750,02/28/18,OK-NC-NY-MN-MI-IN-IL-FL-DE-CA-AL-
0000001750,08/31/18,OK-NC-NY-MN-MI-IN-IL-FL-DE-CA-AL-
0000001800,12/31/02,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,09/30/02,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,06/30/02,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,03/31/02,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,03/31/03,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,12/31/03,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,06/30/03,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,09/30/03,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,03/31/04,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,06/30/04,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,09/30/04,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,12/31/04,PR-VA-UT-TX-OH-NC-NJ-MI-MA-KS-IL-CA-AZ-
0000001800,06/30/05,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,12/31/05,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,09/30/05,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,03/31/05,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,09/30/06,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,06/30/06,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,03/31/06,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,12/31/06,PR-VA-TX-OH-NJ-MI-MA-IL-CO-CA-AZ-
0000001800,12/31/07,PR-VA-TX-OH-NJ-MI-MA-IL-FL-CO-CA-AZ-
0000001800,09/30/07,PR-VA-TX-OH-NJ-MI-MA-IL-FL-CO-CA-AZ-
0000001800,06/30/07,PR-VA-TX-OH-NJ-MI-MA-IL-FL-CO-CA-AZ-
0000001800,03/31/07,PR-VA-TX-OH-NJ-MI-MA-IL-FL-CO-CA-AZ-
0000001800,09/30/08,PR-VA-TX-OH-NJ-MI-MA-IL-CA-AZ-
0000001800,12/31/08,PR-VA-TX-OH-NJ-MI-MA-IL-CA-AZ-
0000001800,06/30/08,PR-VA-TX-OH-NJ-MI-MA-IL-CA-AZ-
0000001800,03/31/08,PR-VA-TX-OH-NJ-MI-MA-IL-CA-AZ-
;;;;

data WORK.have2;
  infile datalines dsd truncover;
  input Year:32. qtr:32. state:$2.;
datalines4;
2003,3,DE
2003,3,MD
2003,3,NC
2003,3,NJ
2003,3,NY
2003,3,PA
2003,3,RI
2003,3,VA
2003,3,VT
2003,3,WV
2004,3,FL
2004,3,GA
2004,3,NC
2004,3,SC
2004,3,VA
2004,3,AL
2004,3,FL
2004,3,GA
2004,3,KY
2004,3,MD
2004,3,NC
2004,3,NY
2004,3,OH
2004,3,PA
2004,3,SC
2004,3,VA
2004,3,WV
2004,3,AL
2004,3,FL
2004,3,GA
2004,3,KY
2004,3,LA
2004,3,MA
2004,3,MD
2004,3,MS
2004,3,NC
2004,3,NH
2004,3,NJ
2004,3,NY
2004,3,OH
2004,3,PA
2004,3,SC
2004,3,TN
2004,3,VA
2004,3,WV
2004,3,DE
2004,3,FL
2004,3,GA
2004,3,MD
2004,3,NC
2004,3,NJ
2004,3,PA
2004,3,SC
2004,3,VA
2005,3,AL
2005,3,AR
2005,3,CT
2005,3,FL
2005,3,GA
2005,3,IA
2005,3,IN
2005,3,KS
2005,3,LA
2005,3,MA
2005,3,MD
2005,3,MI
2005,3,MS
2005,3,NC
2005,3,NJ
2005,3,NY
2005,3,OH
2005,3,PA
2005,3,SC
2005,3,TN
2005,3,VA
2005,3,WV
2005,3,AL
2005,3,AR
2005,3,FL
2005,3,GA
2005,3,IL
2005,3,IN
2005,3,KY
2005,3,LA
2005,3,MA
2005,3,MD
2005,3,ME
2005,3,MI
2005,3,MS
2005,3,NC
2005,3,NH
2005,3,OH
2005,3,PA
2005,3,RI
2005,3,TN
2005,3,VA
2005,3,VT
2005,3,WV
2005,3,AL
2005,3,AR
;;;;

Re: Matching Strings States of Geographic Operation with States Affected by Disaster

Shmuel — Fri, 24 Apr 2020 01:01:50 GMT

Try next code:

proc sort data=have1; by fyr; run;
proc sort data=have2; by year qtr; run;

data temp;
 set have1;
     year = year(fyr);
     qtr  = qtr(fyr);
run;

data want;
 merge temp have2;
   by year qtr;
      if findw(geogr_disper,state,'-')>0 then flag=1;
      else flag=0;
   drop year qtr;
run;

topic Re: Matching Strings States of Geographic Operation with States Affected by Disaster in SAS Programming

Matching Strings States of Geographic Operation with States Affected by Disaster

Re: Matching Strings States of Geographic Operation with States Affected by Disaster