topic Re: Need help with Merge in SAS Programming

Need help with Merge

steveg1 — Sun, 05 Jul 2015 04:47:39 GMT

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;

run;

Re: Need help with Merge

LinusH — Sun, 05 Jul 2015 07:08:46 GMT

For many to many merges never use the data step. Use SQL instead.

Re: Need help with Merge

steveg1 — Sun, 05 Jul 2015 07:32:05 GMT

Thanks.

What would the SQL code look like?

Re: Need help with Merge

PGStats — Sun, 05 Jul 2015 20:01:07 GMT

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)

Re: Need help with Merge

steveg1 — Fri, 10 Jul 2015 03:23:17 GMT

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

Re: Need help with Merge

ballardw — Fri, 10 Jul 2015 15:44:39 GMT

Are your PObox variables actually numeric? If they were character you can get very poor results with > or < comparisons.