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.
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.
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.
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.';
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!!!