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
;;;;
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 |
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;
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;
...
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 |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: