Need help with Merge

Reply
New Contributor
Posts: 4

Need help with Merge

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;

Esteemed Advisor
Posts: 5,198

Re: Need help with Merge

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

Data never sleeps
New Contributor
Posts: 4

Re: Need help with Merge

Thanks.

What would the SQL code look like?

Respected Advisor
Posts: 4,606

Re: Need help with Merge

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

PG
New Contributor
Posts: 4

Re: Need help with Merge

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

Grand Advisor
Posts: 10,211

Re: Need help with Merge

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

Ask a Question
Discussion stats
  • 5 replies
  • 277 views
  • 0 likes
  • 4 in conversation