Hi:
I have a file (pobox_zip) that converts a city/PO box range to a new zip. This input file has ranges of PO boxes within each city, and each range is a separate record.
I have a second input fie that contains specific city/po box numbers and I want to match them to file 1 above by city/PO box and derive the new zip code.
The merge code below is not working. This is a many to many merge at the city code level.
Any ideas?
Thanks
Steve
data pobox_coded;
merge pob (in=a) pobox_zip (in=b);
by city_code;
if a and b then do;
/* a and b means city code match....now look for a PO box range match*/
/* POB is PO box #; PO_from and PO_to are ranges of PO boxes*/
if pob >=PO_from and pob <= PO_to then do;
pob_hit=1;
po_zip5=zip5;
end;
end;
run;
For many to many merges never use the data step. Use SQL instead.
Thanks.
What would the SQL code look like?
It would look like this:
proc sql;
create table data pobox_coded as
select a.*, b.zip5 as po_zip5
from
pob as a left join
pobox_zip as b
on a.city_code=b.city_code and
pob between PO_from and PO_to;
quit;
(untested)
PG
Hi:
With a very minor adjustment the code worked.
Thanks for the tip.
I have used SAS in many to many merges, and it seems fine for match/append data. Strange that i does not work for range data.
Stev
Are your PObox variables actually numeric? If they were character you can get very poor results with > or < comparisons.
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.