Binning Data by a range

jwynne21 — Thu, 22 Sep 2011 14:00:51 GMT

I have a SAS problem that I can’t wrap my head around, and I was hoping maybe you had some insight. I have a list of ip addresses that I need to attach another table to. The issue is the other table doesn’t have distinct ip addresses. It has a range of ip addresses. I’m not sure how to put these ip addresses into their ranges without using a ton of if statements. I put an example of what I am working with below. The first table is my list of ip addresses and the second table is an example of the ip ranges, with startIP being the begining of the range and endIP being the end I need to attach locationID to the ip_num in table1. These are just small example, there are over three million ranges. If you have any possible solutions let me know.

Thanks

Table1

ip_num
1185542109
3278941550
1289704938
1117540729
2325938219
1185008985
1112711511
1214169412
405623619
1184653078

Table2

startIP	endIP	locationID
999784448	999800831	2
1024360536	1024360543	2
1024361008	1024361023	2
1024361096	1024361103	2
1024361488	1024361503	2
1024361568	1024361599	2
1024362336	1024362351	2
1024362400	1024362495	2
1024362688	1024362703	2
1024362736	1024362751	2

Binning Data by a range

art297 — Thu, 22 Sep 2011 14:21:01 GMT

One way to do it could be by creating a format. E.g.,

data Table1;

input ip_num;

cards;

1185542109

3278941550

1289704938

1117540729

2325938219

1185008985

1112711511

1214169412

405623619

1184653078

;

data Table2;

input startIP endIP locationID;

cards;

0 999784447 1

999784448 1124360535 2

1124360536 9999999999 3

;

data prepfmt;

retain fmtname 'ipfmt';

set Table2 (rename=

(startIP=start

endIP=end

locationID=label));

run;

proc sort data=prepfmt;

by start;

run;

proc format cntlin=prepfmt;

run;

data want;

set table1;

locationID=put(ip_num,ipfmt.);

run;

topic Binning Data by a range in SAS Data Science

Binning Data by a range

Binning Data by a range