How do I format an ip dot-notation (long) to a short notation?

[deleted_user]

I usually have INET_ATON in MySql but converting the program to SAS now seems impossible to convert a dot-notation ip adress into a short decimal version eg

255.255.255.255 (converts to) 4294967295 (and back to) 255.255.255.255
209.65.0.0 (converts to) 3510697984 (and back to) 209.65.0.0
12.0.0.0 (converts to) 201326592 (and back to) 12.0.0.0
1.0.0.0 (converts to) 16777216 (and back to) 1.0.0.0

Is there any way to do this that SAS understands?

[Cynthia_sas]

Hi:
Generally, the stored process author doesn't need to worry about converting IP addresses, so I'm not sure how this applies to stored processes. There are some reserved macro variables, described here:
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/reserved.html
related to URLs, but no functions such as what you describe.

Your best bet for help with this question is to conrtact Tech Support. Perhaps you can explain to them how you use this function in MySQL and they can help you figure out whether you still need to do the same thing inside a SAS stored process.

To contact SAS Tech Support, go to http://support.sas.com and look for the link entitled "Submit a Problem" in the left-hand navigation pane under the "Support" heading.

cynthia

[deleted_user]

data ips(drop=i);
* ip to hex to dec process based on http://www.geocities.com/thestarman3/net/decip.html ;

input ip $15.;

*convert ip to hex;
array i2h_ip{4} 3.;
array i2h_hex{4} $2.;
do i = 1 to 4;
i2h_ip(i) = input(scan(ip,i,'.'),3.);
i2h_hex(i) = put(i2h_ip(i), hex2.);
end;
i2h = cats(i2h_hex1, i2h_hex2, i2h_hex3, i2h_hex4);

*convert hex to dec;
h2d = input(i2h, hex.);

*convert dec back to hex;
d2h = put(h2d, hex8.);

*convert hex back to ip;
array h2i_hex{4} $2.;
array h2i_ip{4} 3.;
do i = 1 to 4;
h2i_hex(i) = substr(d2h, i*2-1, 2);
h2i_ip(i) = input(h2i_hex(i), hex.);
end;
h2i = catx('.',h2i_ip1,h2i_ip2,h2i_ip3,h2i_ip4);

if _n_ = 1 then
put 'IP in' @20 'to Decimal' @40 'back to IP';
put ip @20 h2d @40 h2i;
datalines;
255.255.255.255
209.65.0.0
12.0.0.0
1.0.0.0
run;

LOG
IP in to Decimal back to IP
255.255.255.255 4294967295 255.255.255.255
209.65.0.0 3510697984 209.65.0.0
12.0.0.0 201326592 12.0.0.0
1.0.0.0 16777216 1.0.0.0

[Cynthia_sas]

Hi:
Good example of using arrays and data manipulation!

I am still having a hard time figuring out how this would be used inside a stored process. Even though something CAN be done with code, there's still the issue of whether it NEEDS to be done or whether the MySQL process is doing something that is unnecessary in a stored process.

The geocities link explains that this conversion "is part of the normal process that any Internet communications program must go through to arrive at the 32-bit string of binary digits which is actually used to represent this address inside a computer." A stored process program generally does not need to know the IP address or manipulate it in this way. Someone who is writing a custom front end MIGHT need to use such a conversion -- but they would not generally use SAS to write the front end -- they'd write it in some other language or IDE (like Eclipse) and then would call the stored process program.

That's why I recommended contacting Tech Support -- not because it's a hard coding exercise, but because the use inside a stored process is questionable. If the IP address is purely a data item, then perhaps the overhead to store it as a character variable is worth the price of converting it back and forth.

My .02,
cynthia

[deleted_user]

I agree with Cynthia on checking the necessity of performing the conversion before you waste time on coding the solution. But that's because we both believe in conservation.

I was interested to see the method undertaken, and while it is a good example of the techniques she mentioned, I thought it a little long winded.

I have a need to make the conversion where I am trying to find which block of IP address assignments includes a particular address. Since more than one octet may change in any pair of IP addresses, conversion to numeric value is the most reliable way to make the match. I approach this with a statement style macro which is copied below.

Kind regards

David

/*
Convert a standard number on the form 127.0.0.1 into a real number.
You call the macro as a statement, and specify the name of the
original IP number with the parameter IPNAME.
Sample macro call
VISIT = %IPDECODE( IPNAME = CIP);
Outcome of checking 127.0.0.1 is 2130706433
*/

%Macro IPDECODE( IPNAME = /* Name of the IP number variable */
) / Des = 'Incl code, derive IP value from std no.';

Input( Scan( &IPNAME, 1, '.'), 3.) * ( 256 ** 3) +
Input( Scan( &IPNAME, 2, '.'), 3.) * ( 256 ** 2) +
Input( Scan( &IPNAME, 3, '.'), 3.) * ( 256 ** 1) +
Input( Scan( &IPNAME, 4, '.'), 3.) * ( 256 ** 0);

%MEnd IPDECODE;

[deleted_user]

i also thought my solution was a bit long winded, but after seeing dkvj's solution, i'm convinced his is the best solution presented.

very well done

[deleted_user]

Thank you Cynthia and Richard. I spent a little while doing this a few years back so I could match data for web server logs.

I shall also point out that an IP address (IP-V4) has four octets with each octet being three character bytes, delimited by decimal points. So the longest length for an IP address in a table is 15 bytes.

As long as the magnitude of the largest IP value is within the precise numerical representation of the operating system, then you can store this value in an 8 byte numeric column. That saves 7 bytes per record for what might be many tens of thousands of server log entries.

As long as you can convert that numeric value back to a human readable four octet representation, then it makes good sense to save the space. Converting back is simply a matter of dividing by the appropriate values, using modulus, and representing it as a character value. Once again, you can code that as a statement style macro which is very efficient for compilation, memory and execution.

Note that all bets are off when IP-V6 comes in since that will change the data structures. So it is a solution with an expiry date!!!

Kind regards

David