BookmarkSubscribeRSS Feed
steveg1
Calcite | Level 5

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;

5 REPLIES 5
LinusH
Tourmaline | Level 20

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

Data never sleeps
steveg1
Calcite | Level 5

Thanks.

What would the SQL code look like?

PGStats
Opal | Level 21

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
steveg1
Calcite | Level 5

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

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 594 views
  • 0 likes
  • 4 in conversation