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 |
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.