Hello Ethan @t75wez1 ,
Once you have the MaxMind data saved as CSV files and in a single folder location, you can use SAS code to import the CSVs into SAS data sets then merge the city location details into both the IPv4 and IPv6 SAS data sets.
Import and merge code is below, you will need to update the paths listed before running.
libname geo "<insert input data path here>";
libname out "<insert output data path here>";
*City IPv4 data;
DATA geo.CITY_BLOCKS_IPV4;
LENGTH
network $ 18
geoname_id 8
registered_country_geoname_id 8
represented_country_geoname_id 8
is_anonymous_proxy 8
is_satellite_provider 8
postal_code $ 8
latitude 8
longitude 8
accuracy_radius 8 ;
FORMAT
network $CHAR18.
geoname_id BEST8.
registered_country_geoname_id BEST7.
represented_country_geoname_id BEST7.
is_anonymous_proxy BEST1.
is_satellite_provider BEST1.
postal_code $CHAR8.
latitude BEST8.
longitude BEST9.
accuracy_radius BEST4. ;
INFORMAT
network $CHAR18.
geoname_id BEST8.
registered_country_geoname_id BEST7.
represented_country_geoname_id BEST7.
is_anonymous_proxy BEST1.
is_satellite_provider BEST1.
postal_code $CHAR8.
latitude BEST8.
longitude BEST9.
accuracy_radius BEST4. ;
INFILE '<insert input data path here>\GeoLite2-City-Blocks-IPv4.csv'
FIRSTOBS=2
DLM=','
MISSOVER
DSD ;
INPUT
network : $CHAR18.
geoname_id : ?? BEST8.
registered_country_geoname_id : ?? BEST7.
represented_country_geoname_id : ?? BEST7.
is_anonymous_proxy : ?? BEST1.
is_satellite_provider : ?? BEST1.
postal_code : $CHAR8.
latitude : ?? COMMA8.
longitude : ?? COMMA9.
accuracy_radius : ?? BEST4. ;
RUN;
*City IPv6 Data;
DATA geo.CITY_BLOCKS_IPV6;
LENGTH
network $ 43
geoname_id 8
registered_country_geoname_id 8
represented_country_geoname_id $ 1
is_anonymous_proxy 8
is_satellite_provider 8
postal_code $ 8
latitude 8
longitude 8
accuracy_radius 8 ;
FORMAT
network $CHAR43.
geoname_id BEST8.
registered_country_geoname_id BEST7.
represented_country_geoname_id $CHAR1.
is_anonymous_proxy BEST1.
is_satellite_provider BEST1.
postal_code $CHAR8.
latitude BEST8.
longitude BEST9.
accuracy_radius BEST4. ;
INFORMAT
network $CHAR43.
geoname_id BEST8.
registered_country_geoname_id BEST7.
represented_country_geoname_id $CHAR1.
is_anonymous_proxy BEST1.
is_satellite_provider BEST1.
postal_code $CHAR8.
latitude BEST8.
longitude BEST9.
accuracy_radius BEST4. ;
INFILE '<insert input data path here>\GeoLite2-City-Blocks-IPv6.csv'
FIRSTOBS=2
DLM=','
MISSOVER
DSD ;
INPUT
network : $CHAR43.
geoname_id : ?? BEST8.
registered_country_geoname_id : ?? BEST7.
represented_country_geoname_id : $CHAR1.
is_anonymous_proxy : ?? BEST1.
is_satellite_provider : ?? BEST1.
postal_code : $CHAR8.
latitude : ?? COMMA8.
longitude : ?? COMMA9.
accuracy_radius : ?? BEST4. ;
RUN;
*City Locations data;
DATA geo.CITY_LOCATIONS_EN;
LENGTH
geoname_id 8
locale_code $ 2
continent_code $ 2
continent_name $ 13
country_iso_code $ 2
country_name $ 44
subdivision_1_iso_code $ 3
subdivision_1_name $ 52
subdivision_2_iso_code $ 3
subdivision_2_name $ 39
city_name $ 72
metro_code 8
time_zone $ 30
is_in_european_union 8 ;
FORMAT
geoname_id BEST8.
locale_code $CHAR2.
continent_code $CHAR2.
continent_name $CHAR13.
country_iso_code $CHAR2.
country_name $CHAR44.
subdivision_1_iso_code $CHAR3.
subdivision_1_name $CHAR52.
subdivision_2_iso_code $CHAR3.
subdivision_2_name $CHAR39.
city_name $CHAR72.
metro_code BEST3.
time_zone $CHAR30.
is_in_european_union BEST1. ;
INFORMAT
geoname_id BEST8.
locale_code $CHAR2.
continent_code $CHAR2.
continent_name $CHAR13.
country_iso_code $CHAR2.
country_name $CHAR44.
subdivision_1_iso_code $CHAR3.
subdivision_1_name $CHAR52.
subdivision_2_iso_code $CHAR3.
subdivision_2_name $CHAR39.
city_name $CHAR72.
metro_code BEST3.
time_zone $CHAR30.
is_in_european_union BEST1. ;
INFILE '<insert input data path here>\GeoLite2-City-Locations-en.csv'
FIRSTOBS=2
DLM=','
MISSOVER
DSD ;
INPUT
geoname_id : ?? BEST8.
locale_code : $CHAR2.
continent_code : $CHAR2.
continent_name : $CHAR13.
country_iso_code : $CHAR2.
country_name : $CHAR44.
subdivision_1_iso_code : $CHAR3.
subdivision_1_name : $CHAR52.
subdivision_2_iso_code : $CHAR3.
subdivision_2_name : $CHAR39.
city_name : $CHAR72.
metro_code : ?? BEST3.
time_zone : $CHAR30.
is_in_european_union : ?? BEST1. ;
RUN;
*Merge in City information before further processing;
proc sql;
create table out.geoip_range_city_ipv4 as
select ip.*,
locale_code,
continent_code,
continent_name,
country_iso_code,
country_name,
subdivision_1_iso_code,
subdivision_1_name,
subdivision_2_iso_code,
subdivision_2_name,
city_name,
metro_code,
time_zone,
is_in_european_union
from geo.city_blocks_ipv4 as ip
left join geo.city_locations_en as city on (ip.geoname_id = city.geoname_id)
;
create table out.geoip_range_city_ipv6 as
select ip.*,
locale_code,
continent_code,
continent_name,
country_iso_code,
country_name,
subdivision_1_iso_code,
subdivision_1_name,
subdivision_2_iso_code,
subdivision_2_name,
city_name,
metro_code,
time_zone,
is_in_european_union
from geo.city_blocks_ipv6 as ip
left join geo.city_locations_en as city on (ip.geoname_id = city.geoname_id)
;
quit;
Hope that helps!
Agata
... View more
