GeoWorlMapData.sas
Please edit the value for parmsl in line 1 and set it to an appropriate value for your environment.
libname parmsl "C:\My Files\Map1";
%let p_libRef=parmsl;
data world1;
set maps.world;
ord+1;
run;
/* Create and populate the _BASE table from the original table and rename two columns */
proc sql noprint;
create table world as
select -1*long as x, lat as y, segment, cont, id as state,ord, density
from world1
where(density<=1) and (segment<=3) and (state ^= 143) and (state ^=405) and (state ^=284)
; /*leave out antarctica, Greenland and Cocos Island*/
create table names as
select name, id as state
from maps.names
;
create table proj as
select a.*, b.name as areaname
from work.world as a left join work.names as b
on a.state=b.state
;
create table &p_libRef..proj_world_base (rename=(areaname=areaname_base groupname=groupname_base))
as
select *,
case cont
when 91 then 'North America'
when 92 then 'South America'
when 93 then 'Europe'
when 94 then 'Africa'
when 95 then 'Asia'
when 96 then 'Australia'
end as groupname
from proj
;
alter table &p_libRef..proj_world_base
add
countryKey char(10),
contKey char(10)
;
update &p_libRef..proj_world_base
set countryKey=left(put(state,10.))
;
update &p_libRef..proj_world_base
set contKey=left(put(Cont,10.))
;
/* Create NLS tables */
create table &p_libRef..proj_world_area_NLS(
LOCALE char(5),
KEY char(10),
LINENO numeric(5),
TEXT char(60),
constraint primarykey primary key (LOCALE, KEY, LINENO )
);
create table &p_libRef..proj_world_group_NLS(
LOCALE char(5),
KEY char(10),
LINENO numeric(5),
TEXT char(60),
constraint primarykey primary key (LOCALE, KEY, LINENO )
);
/* Populate NLS tables */
insert into &p_libRef..proj_world_area_NLS
(LOCALE,KEY,LINENO,TEXT)
values('en_US','110',1,'Afghanistan')
values('en_US','120',1,'Albania')
values('en_US','125',1,'Algeria')
values('en_US','60',1,'American Samoa')
values('en_US','140',1,'Andorra')
values('en_US','141',1,'Angola')
values('en_US','142',1,'Anguilla')
values('en_US','149',1,'Antigua/Barbuda')
values('en_US','150',1,'Argentina')
values('en_US','135',1,'Armenia')
values('en_US','100',1,'Aruba')
values('en_US','155',1,'Ashmore/Cartier')
values('en_US','160',1,'Australia')
values('en_US','165',1,'Austria')
values('en_US','115',1,'Azerbaijan')
values('en_US','180',1,'Bahamas')
values('en_US','181',1,'Bahrain')
values('en_US','64',1,'Baker Island')
values('en_US','182',1,'Bangladesh')
values('en_US','184',1,'Barbados')
values('en_US','187',1,'Bassas Da India')
values('en_US','211',1,'Belarus')
values('en_US','190',1,'Belgium')
values('en_US','227',1,'Belize')
values('en_US','311',1,'Benin')
values('en_US','195',1,'Bermuda')
values('en_US','200',1,'Bhutan')
values('en_US','205',1,'Bolivia')
values('en_US','185',1,'Bosnia/Herzegovina')
values('en_US','210',1,'Botswana')
values('en_US','212',1,'Bouvet Island')
values('en_US','220',1,'Brazil')
values('en_US','228',1,'British Indian Ocean')
values('en_US','231',1,'British Virgin Islands')
values('en_US','232',1,'Brunei')
values('en_US','245',1,'Bulgaria')
values('en_US','927',1,'Burkina Faso')
values('en_US','252',1,'Burundi')
values('en_US','255',1,'Cambodia')
values('en_US','257',1,'Cameroon')
values('en_US','260',1,'Canada')
values('en_US','264',1,'Cape Verde')
values('en_US','268',1,'Cayman Islands')
values('en_US','269',1,'Central African Rep.')
values('en_US','273',1,'Chad')
values('en_US','275',1,'Chile')
values('en_US','280',1,'China')
values('en_US','516',1,'Christmas Island')
values('en_US','282',1,'Clipperton Island')
values('en_US','285',1,'Colombia')
values('en_US','286',1,'Comoros')
values('en_US','290',1,'Congo')
values('en_US','293',1,'Cook Islands')
values('en_US','294',1,'Coral Sea Islands')
values('en_US','295',1,'Costa Rica')
values('en_US','440',1,'Croatia')
values('en_US','300',1,'Cuba')
values('en_US','305',1,'Cyprus')
values('en_US','310',1,'Czech Republic')
values('en_US','315',1,'Denmark')
values('en_US','317',1,'Djibouti')
values('en_US','318',1,'Dominica')
values('en_US','320',1,'Dominican Republic')
values('en_US','325',1,'Ecuador')
values('en_US','922',1,'Egypt')
values('en_US','330',1,'El Salvador')
values('en_US','332',1,'Equatorial Guinea')
values('en_US','327',1,'Eritrea')
values('en_US','331',1,'Estonia')
values('en_US','335',1,'Ethiopia')
values('en_US','334',1,'Europa Island')
values('en_US','337',1,'Falkland Islands')
values('en_US','336',1,'Faroe Islands')
values('en_US','338',1,'Fiji')
values('en_US','340',1,'Finland')
values('en_US','350',1,'France')
values('en_US','355',1,'French Guiana')
values('en_US','367',1,'French Polynesia')
values('en_US','369',1,'French Southern Terr.')
values('en_US','388',1,'Gabon')
values('en_US','389',1,'Gambia')
values('en_US','393',1,'Gaza Strip')
values('en_US','390',1,'Georgia')
values('en_US','394',1,'Germany')
values('en_US','396',1,'Ghana')
values('en_US','397',1,'Gibraltar')
values('en_US','399',1,'Glorioso Islands')
values('en_US','400',1,'Greece')
values('en_US','406',1,'Grenada')
values('en_US','407',1,'Guadeloupe')
values('en_US','66',1,'Guam')
values('en_US','415',1,'Guatemala')
values('en_US','416',1,'Guernsey')
values('en_US','417',1,'Guinea')
values('en_US','737',1,'Guinea-Bissau')
values('en_US','418',1,'Guyana')
values('en_US','420',1,'Haiti')
values('en_US','424',1,'Heard/Mcdonald')
values('en_US','430',1,'Honduras')
values('en_US','435',1,'Hong Kong')
values('en_US','65',1,'Howland Island')
values('en_US','445',1,'Hungary')
values('en_US','450',1,'Iceland')
values('en_US','455',1,'India')
values('en_US','458',1,'Indonesia')
values('en_US','460',1,'Iran')
values('en_US','465',1,'Iraq')
values('en_US','470',1,'Ireland')
values('en_US','475',1,'Israel')
values('en_US','480',1,'Italy')
values('en_US','485',1,'Ivory Coast')
values('en_US','487',1,'Jamaica')
values('en_US','488',1,'Jan Mayen Islands')
values('en_US','490',1,'Japan')
values('en_US','62',1,'Jarvis Island')
values('en_US','495',1,'Jersey')
values('en_US','67',1,'Johnston Atoll')
values('en_US','500',1,'Jordan')
values('en_US','497',1,'Juan De Nova Island')
values('en_US','525',1,'Kazakhstan')
values('en_US','505',1,'Kenya')
values('en_US','68',1,'Kingman Reef')
values('en_US','398',1,'Kiribati')
values('en_US','514',1,'Korea, North')
values('en_US','515',1,'Korea, South')
values('en_US','520',1,'Kuwait')
values('en_US','510',1,'Kyrgyzstan')
values('en_US','530',1,'Laos')
values('en_US','541',1,'Latvia')
values('en_US','540',1,'Lebanon')
values('en_US','543',1,'Lesotho')
values('en_US','545',1,'Liberia')
values('en_US','550',1,'Libya')
values('en_US','553',1,'Liechtenstein')
values('en_US','542',1,'Lithuania')
values('en_US','570',1,'Luxembourg')
values('en_US','573',1,'Macau')
values('en_US','574',1,'Macedonia')
values('en_US','575',1,'Madagascar')
values('en_US','577',1,'Malawi')
values('en_US','580',1,'Malaysia')
values('en_US','583',1,'Maldives')
values('en_US','585',1,'Mali')
values('en_US','590',1,'Malta')
values('en_US','588',1,'Man')
values('en_US','73',1,'Marshall Islands')
values('en_US','591',1,'Martinique')
values('en_US','592',1,'Mauritania')
values('en_US','593',1,'Mauritius')
values('en_US','594',1,'Mayotte')
values('en_US','595',1,'Mexico')
values('en_US','63',1,'Micronesia')
values('en_US','71',1,'Midway Island')
values('en_US','576',1,'Moldova')
values('en_US','607',1,'Monaco')
values('en_US','608',1,'Mongolia')
values('en_US','609',1,'Montserrat')
values('en_US','610',1,'Morocco')
values('en_US','615',1,'Mozambique')
values('en_US','250',1,'Myanmar')
values('en_US','821',1,'Namibia')
values('en_US','621',1,'Nauru')
values('en_US','61',1,'Navassa Island')
values('en_US','625',1,'Nepal')
values('en_US','630',1,'Netherlands')
values('en_US','640',1,'Netherlands Antilles')
values('en_US','645',1,'New Caledonia')
values('en_US','660',1,'New Zealand')
values('en_US','665',1,'Nicaragua')
values('en_US','667',1,'Niger')
values('en_US','670',1,'Nigeria')
values('en_US','672',1,'Niue')
values('en_US','683',1,'Norfolk Island')
values('en_US','69',1,'Northern Mariana Islands')
values('en_US','685',1,'Norway')
values('en_US','616',1,'Oman')
values('en_US','700',1,'Pakistan')
values('en_US','75',1,'Palau')
values('en_US','70',1,'Palmyra Atoll')
values('en_US','710',1,'Panama')
values('en_US','712',1,'Papua New Guinea')
values('en_US','714',1,'Paracel Islands')
values('en_US','715',1,'Paraguay')
values('en_US','720',1,'Peru')
values('en_US','725',1,'Philippines')
values('en_US','727',1,'Pitcairn Islands')
values('en_US','730',1,'Poland')
values('en_US','735',1,'Portugal')
values('en_US','72',1,'Puerto Rico')
values('en_US','747',1,'Qatar')
values('en_US','750',1,'Reunion')
values('en_US','755',1,'Romania')
values('en_US','825',1,'Russia')
values('en_US','758',1,'Rwanda')
values('en_US','763',1,'Saint Kitts/Nevis')
values('en_US','775',1,'Saint Vincent/Grenadines')
values('en_US','963',1,'Samoa')
values('en_US','782',1,'San Marino')
values('en_US','783',1,'Sao Tome/Principe')
values('en_US','785',1,'Saudi Arabia')
values('en_US','787',1,'Senegal')
values('en_US','788',1,'Seychelles')
values('en_US','790',1,'Sierra Leone')
values('en_US','795',1,'Singapore')
values('en_US','548',1,'Slovakia')
values('en_US','789',1,'Slovenia')
values('en_US','229',1,'Solomon Islands')
values('en_US','800',1,'Somalia')
values('en_US','801',1,'South Africa')
values('en_US','830',1,'Spain')
values('en_US','833',1,'Spratly Islands')
values('en_US','272',1,'Sri Lanka')
values('en_US','765',1,'St. Helena')
values('en_US','770',1,'St. Lucia')
values('en_US','773',1,'St. Pierre/Miquelon')
values('en_US','835',1,'Sudan')
values('en_US','840',1,'Suriname')
values('en_US','845',1,'Svalbard')
values('en_US','847',1,'Swaziland')
values('en_US','850',1,'Sweden')
values('en_US','855',1,'Switzerland')
values('en_US','858',1,'Syria')
values('en_US','281',1,'Taiwan')
values('en_US','784',1,'Tajikistan')
values('en_US','865',1,'Tanzania')
values('en_US','875',1,'Thailand')
values('en_US','883',1,'Togo')
values('en_US','884',1,'Tokelau')
values('en_US','886',1,'Tonga')
values('en_US','887',1,'Trinidad And Tobago')
values('en_US','889',1,'Tromelin Island')
values('en_US','890',1,'Tunisia')
values('en_US','905',1,'Turkey')
values('en_US','909',1,'Turkmenistan')
values('en_US','906',1,'Turks/Caicos Islands')
values('en_US','910',1,'Uganda')
values('en_US','928',1,'Ukraine')
values('en_US','888',1,'United Arab Emirates')
values('en_US','925',1,'United Kingdom')
values('en_US','926',1,'United States')
values('en_US','930',1,'Uruguay')
values('en_US','931',1,'Uzbekistan')
values('en_US','651',1,'Vanuatu')
values('en_US','940',1,'Venezuela')
values('en_US','945',1,'Vietnam')
values('en_US','78',1,'Virgin Islands (U.S.)')
values('en_US','80',1,'Wake Island')
values('en_US','950',1,'Wallis/Futuna Islands')
values('en_US','955',1,'West Bank')
values('en_US','831',1,'Western Sahara')
values('en_US','965',1,'Yemen')
values('en_US','970',1,'Yugoslavia')
values('en_US','291',1,'Zaire')
values('en_US','990',1,'Zambia')
values('en_US','818',1,'Zimbabwe');
insert into &p_libRef..proj_world_group_NLS
(LOCALE,KEY,LINENO,TEXT)
values('en_US','94',1,'Africa')
values('en_US','95',1,'Asia')
values('en_US','96',1,'Australia')
values('en_US','93',1,'Europe')
values('en_US','91',1,'North America')
values('en_US','92',1,'South America');
/* Delete the original table */
drop table &p_libref..proj_world;
quit;
proc sort data = PARMSL.proj_world_base;
by ord ;
run;
proc sql noprint;
/* Create the view which shows locale approriate labels and formats */
create view &p_libRef..proj_world as
select a.x,
a.y,
a.segment,
a.areaname_base,
a.groupname_base,
a.state,
a.cont,
a.density,
b.TEXT as areaname,
c.TEXT as groupname
from &p_libRef..proj_world_BASE as a,
&p_libRef..proj_world_area_NLS as b,
&p_libRef..proj_world_group_NLS as c
where a.countryKey eq b.key and
upcase(getpxlocale()) eq upcase(b.locale)
and a.contKey eq c.key and
upcase(getpxlocale()) eq upcase(c.locale)
;
quit;
