cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9 Agent1592
Pyrite | Level 9
Go to Solution

Matching Strings States of Geographic Operation with States Affected by Disaster

Posted 04-23-2020 08:04 PM (692 views)

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
;;;;

 

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18 Shmuel
Garnet | Level 18

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

Posted 04-23-2020 09:01 PM (661 views)  |   In reply to Agent1592

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;

View solution in original post

1 REPLY 1
Shmuel
Garnet | Level 18 Shmuel
Garnet | Level 18

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

Posted 04-23-2020 09:01 PM (662 views)  |   In reply to Agent1592

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • ‎04-23-2020 08:04 PM
  • 693 views
  • 1 like
  • 2 in conversation