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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.