Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Binning Data by a range

Reply
New Contributor
Posts: 3

Binning Data by a range

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

PROC Star
Posts: 7,480

Binning Data by a range

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;

Ask a Question
Discussion stats
  • 1 reply
  • 331 views
  • 0 likes
  • 2 in conversation