Match string variables from two columns separated by a dash and create a dummy based on the match

Agent1592 — Fri, 24 Apr 2020 02:41:09 GMT

Dear SAS community:

I have two columns. The first one has the geographic dispersion of a firm by states. The states are abbreviated by two letters and separated by a dash.

The second column has the states that were affected by some kind of disaster. I am interested in creating a dummy variable equal to 1 if the geogr_disper variable contains any of the affected_states. What will be a best option for this. I tried findw but it did not work.

data WORK.have;
  infile datalines dsd truncover;
  input geogr_disper:$200. affected_state:$200.;
datalines4;
WI-TX-OH-NH-MS-ME-KY-GA-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-TX-PA-IN-GA-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-PA-OK-OH-NY-LA-KS-IN-IL-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
OR-NY-NV-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
FL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-NY-IN-IL-HI-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-VT-UT-TX-TN-PA-OK-OH-NY-NJ-NH-MO-MS-MN-MI-LA-KY-IL-FL-DE-CT-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-NJ-NV-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-NH-DE-CT-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PA-IL-FL-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-WI-WV-WA-VA-VT-UT-TX-RI-PA-OR-OH-NC-NY-NJ-NH-NV-MO-MI-MA-MD-ME-KS-IN-IL-ID-HI-FL-DE-CT-CO-CA-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-WV-VA-TX-TN-PA-OH-NY-NM-NJ-NV-MS-MD-IN-IL-FL-DE-CT-CA-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-SD-OK-NE-MO-KS-IL-CO-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-TN-NY-MA-IL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WV-VA-TX-SC-PA-NY-NJ-CT-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PA-NY-NJ-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WV-VA-TN-SC-PA-OH-NC-NY-NJ-MI-MD-KY-IN-GA-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
UT-TN-PA-OH-NY-NE-MI-IL-CA-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-WA-OR-FL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-WA-OH-MN-IL-ID-FL-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
MA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-VT-TX-SC-RI-PA-OR-OH-NC-MS-MN-IN-GA-FL-CT-CA-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PA-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-OK-NM-CO-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
OH-NY-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-SC-MT-LA-IL-FL-DE-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NC-MI-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-NC-NY-NJ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-NC-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WV-VA-NC-NY-NJ-NV-CT-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-NY-KY-IL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-TN-NV-LA-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-TX-TN-OR-NY-NJ-NH-NV-MA-LA-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-PA-OH-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NJ-IL-GA-FL-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-NV-MS-KY-DE-CA-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-NJ-IN-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-OK-NM-MS-MI-LA-KS-DE-CA-AR-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NC-MD-IL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
MO-KS-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PA-OR-NY-MN-DE-CO-CA-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-WV-WA-VA-VT-TX-TN-SC-RI-PA-OK-OH-ND-NY-NJ-MT-MO-MS-MN-MI-MA-MD-ME-LA-KY-KS-IA-IN-IL-GA-FL-DE-CT-CO-CA-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-MS-LA-FL-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-NJ-DE-CT-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-TN-NV-GA-FL-DE-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
IL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-UT-TX-OK-NV-LA-CO-CA-AR-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
OH-NM-MI-IL-FL-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-TN-OH-MS-LA-KY-IN-IL-AR-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-VA-VT-TX-TN-SD-SC-RI-OK-ND-NC-NY-NJ-NH-NV-NE-MO-MS-MA-MD-ME-LA-KY-KS-IA-GA-FL-CT-CA-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
MA-KY-IN-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NH-MA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-MO-FL-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-WA-VA-VT-TX-ND-NC-MT-MN-DE-CO-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
IL-DE-CT-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-TX-PA-OH-NJ-LA-KS-IA-IN-GA-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-OR-NV-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-UT-RI-PA-NY-NJ-DE-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-NY-NJ-DE-CA-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-VT-RI-PA-NY-NJ-NH-NV-MA-MD-ME-KY-DE-CT-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-VA-TX-TN-SC-PA-NC-MS-MN-MD-KY-GA-FL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-WI-WA-VA-TX-TN-SD-SC-PA-OK-NC-NY-NM-NH-NE-MO-MS-ME-KY-KS-IA-IN-IL-ID-HI-GA-DE-AR-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NY-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-WA-VA-UT-TX-TN-SD-SC-OR-OK-ND-NC-NY-NM-NV-NE-MT-MO-MS-MI-LA-KY-KS-IA-ID-HI-GA-FL-CO-CA-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-UT-NY-NM-IA-GA-CT-CO-AZ-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WV-VA-UT-TX-TN-SC-PA-OK-OH-NY-MS-MI-LA-KY-IN-IL-FL-DE-CO-AR-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
KY-GA-FL-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-UT-TX-OR-NY-MI-MD-HI-GA-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-VA-PA-OH-NY-NJ-MD-GA-DE-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-WV-VA-TX-RI-PA-OK-OH-NY-NJ-NV-MO-MN-MA-MD-KY-KS-IN-FL-DE-CO-CA-AR-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WV-TX-LA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-MO-MS-MI-KY-IA-IL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PA-NY-NJ-IL-DE-CT-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-NC-NY-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WI-WV-WA-VA-UT-TX-TN-SC-PA-OR-OK-OH-NC-NY-NJ-NV-NE-MO-MS-MN-MI-MD-LA-KY-KS-IA-IN-IL-HI-GA-FL-CO-CA-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-PA-NY-NJ-IL-FL-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
LA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-VA-UT-NC-NE-KS-IA-IL-FL-DE-CO-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
OH-MO-KS-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
LA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WV-VA-UT-TX-TN-SC-PA-OK-OH-NY-MS-MI-LA-KY-IN-IL-FL-DE-CO-AR-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
VA-UT-TN-SC-PA-OH-NC-NY-NH-KY-KS-IN-IL-FL-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PA-MS-MD-LA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
TX-OH-MO-MS-KY-GA-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
NJ-IL-GA-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WA-NY-IN-IL-HI-DE-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-WY-WI-WV-WA-VA-VT-UT-TX-TN-SD-SC-RI-PA-OR-OK-OH-ND-NC-NY-NM-NJ-NH-NV-NE-MT-MO-MS-MN-MI-MA-MD-ME-LA-KY-KS-IA-IN-IL-ID-HI-GA-FL-DE-CT-CO-CA-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
OH-KY-IN-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-WI-WA-VA-UT-TX-TN-SC-PA-OR-OK-OH-NC-NY-NM-NJ-NH-NV-MO-MS-MN-MI-MD-LA-KY-KS-IA-IN-IL-GA-FL-DE-CO-CA-AR-AZ-AL-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
PR-DE-CO-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
WY-WA-OR-NY-MT-ID-CA-,DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV
;;;;

Re: Match string variables from two columns separated by a dash and create a dummy based on the matc

sustagens — Fri, 24 Apr 2020 03:21:28 GMT

Try this

data want;
set have;

len_geogr=length(compress(geogr_disper,,'a'));
len_affct=length(compress(affected_state,,'a'));

format matched_geogr $200.;

do i=1 to len_geogr;
	curr_geogr=scan(geogr_disper,i,'-','m');

	do j=1 to len_affct;
		curr_affct=scan(affected_state,j,'-','m');
		if curr_geogr=curr_affct then do;
			matched_geogr=catx(" ",matched_geogr,curr_geogr);
			dummy=1;
			end;
	end;
end;

if dummy ne 1 then dummy=0;

drop len_geogr len_affct i j curr_affct curr_geogr;
run;

Re: Match string variables from two columns separated by a dash and create a dummy based on the matc

PGStats — Fri, 24 Apr 2020 03:19:31 GMT

Could be done with regular expressions:

data WORK.have;
  infile datalines dsd truncover;
  input geogr_disper:$200. affected_state:$200.;
  affected = prxMatch(
      cats("/", translate(substr(geogr_disper,1,length(geogr_disper)-1),"|","-"), "/"), 
      affected_state) > 0;
datalines4;
...

Re: Match string variables from two columns separated by a dash and create a dummy based on the matc

ChrisNZ — Fri, 24 Apr 2020 04:18:34 GMT

Or:

  AFFECTED = prxMatch(
               cats('/', translate(AFFECTED_STATE,'|','-'), '/') 
              ,trim(GEOGR_DISPER)) > 0 ;

GEOGR_DISPER	AFFECTED_STATE	AFFECTED
WI-TX-OH-NH-MS-ME-KY-GA-CA-	DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV	0
WA-TX-PA-IN-GA-CA-	DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV	1
TX-PA-OK-OH-NY-LA-KS-IN-IL-AL-	DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV	1
OR-NY-NV-CA-	DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV	1
FL-	DE-MD-NC-NJ-NY-PA-RI-VA-VT-WV	0

topic Match string variables from two columns separated by a dash and create a dummy based on the match in SAS Programming

Match string variables from two columns separated by a dash and create a dummy based on the match

Re: Match string variables from two columns separated by a dash and create a dummy based on the matc

Re: Match string variables from two columns separated by a dash and create a dummy based on the matc

Re: Match string variables from two columns separated by a dash and create a dummy based on the matc