/*
Import the gapminder data

http://www.gapminder.org/data/
*/

libname here '.';

PROC IMPORT OUT=gapminder_income_pp DATAFILE="gapminder_gdp_per_capita_ppp.xlsx" DBMS=EXCEL REPLACE;
 RANGE="Data$"; 
 GETNAMES=YES;
 MIXED=NO;
 SCANTEXT=YES;
 USEDATE=YES;
 SCANTIME=YES;
RUN;

proc transpose data=gapminder_income_pp out=gapminder_income_pp;
by f1 notsorted;
run;

data gapminder_income_pp (keep=country year income_pp); 
 format year comma4.0;
 format country $32.;
 format income_pp dollar8.0;
 set gapminder_income_pp (where=(col1^=.));
country=f1;
year=.; year=_label_;
income_pp=.; income_pp=col1;
run;



PROC IMPORT OUT=gapminder_life_expectancy_pp DATAFILE="gapminder_life_expectancy_at_birth.xlsx" DBMS=EXCEL REPLACE;
 RANGE="Data$"; 
 GETNAMES=YES;
 MIXED=NO;
 SCANTEXT=YES;
 USEDATE=YES;
 SCANTIME=YES;
RUN;

proc transpose data=gapminder_life_expectancy_pp (rename=(life_expectancy_at_birth=f1))
 out=gapminder_life_expectancy_pp;
by f1 notsorted;
run;

data gapminder_life_expectancy_pp (keep=country year life_expectancy); 
 format year comma4.0;
 format country $32.;
 format life_expectancy comma5.1;
 set gapminder_life_expectancy_pp (where=(col1^=.));
country=f1;
year=.; year=_label_;
life_expectancy=.; life_expectancy=col1;
run;



PROC IMPORT OUT=gapminder_population DATAFILE="gapminder_population.xlsx" DBMS=EXCEL REPLACE;
 RANGE="Data$"; 
 GETNAMES=YES;
 MIXED=NO;
 SCANTEXT=YES;
 USEDATE=YES;
 SCANTIME=YES;
RUN;

proc transpose data=gapminder_population (rename=(total_population=f1))
 out=gapminder_population;
by f1 notsorted;
run;

data gapminder_population (keep=country year population); 
 format year comma4.0;
 format country $32.;
 format population comma20.0;
 set gapminder_population (where=(col1^=.));
country=f1;
year=.; year=_label_;
population=.; population=col1;
run;





/* Now combine the 2 data sets */
proc sql;

create table gapminder_data as
select *
from gapminder_income_pp, gapminder_life_expectancy_pp
where (gapminder_income_pp.year=gapminder_life_expectancy_pp.year) and
      (gapminder_income_pp.country=gapminder_life_expectancy_pp.country)
order by year, country;

create table gapminder_data as
select *
from gapminder_data, gapminder_population
where (gapminder_data.year=gapminder_population.year) and
      (gapminder_data.country=gapminder_population.country)
order by year, country;

quit; run;

/* Assign each country to a region */
data gapminder_data; set gapminder_data;
length region $50;
region='unknown';

if country='United States' then country='USA';

if country in ('USA' 'Canada' 'Mexico' 'Haiti' 'Bolivia' 'Nicaragua' 'Honduras'
 'Colombia' 'Guatemala' 'El Salvador' 'Brazil' 'Argentina' 'Panama' 'Uruguay' 'Chile' 
 'Argentina' 'Barbados' 'Trinidad and Tobago' 'Suriname' 'Costa Rica' 'Cuba' 'Peru'
 'Jamaica' 'Dominican Rep.' 'Belize' 'Bahamas' 'Venezuela' 'Saint Lucia' 
 'Ecuador' 'Saint Vincent and the Grenadines' 'Guyana' 'Paraguay' 'Puerto Rico'
 'Netherlands Antilles' 'Grenada' 'Martinique' 'Guadeloupe' 'French Guiana' 'Aruba'
 ) 
 then region='America';

if country in ('South Africa' 'Nigeria' 'Congo, Dem. Rep' 'Burundi' 'Zimbabwe' 'Liberia'
 'Eritrea' 'Madagascar' 'Ghana' 'Sudan' 'South Sudan' 'Congo, Rep.' 'Namibia' 'Gabon'
 'Botswana' 'Equatorial Guinea' 'Swaziland' 'Angola' 'Cameroon' 'Lesotho' 'Chad' 'Zambia'
 'Congo, Dem. Rep.' 'Niger' 'Gambia' 'Ethiopia' 'Tanzania' 'Kenya' 'Malawi' 'Guinea'
 'Togo' 'Rwanda' 'Benin' 'Uganda' 'Guinea-Bissau' 'Central African Rep.' 'Sierra Leone'
 'Mozambique' 'Somalia' 'Mali' 'Uganda' 'Burkina Faso' "Cote d'Ivoire" 'Comoros' 
 'Senegal' 'Mauritania' 'Sao Tome and Principe' 'Mauritius' 'Cape Verde'
 'Reunion') 
 then region='Sub-Saharan Africa';

if country in ('China' 'Japan' 'Vietnam' 'Indonesia' 'Korea, Dem. Rep.' 'Myanmar'
 'Cambodia' 'Papua New Guinea' 'Timor-Leste' 'Philippines' 'Mongolia'
 'Samoa' 'Malaysia' 'Solomon Islands' 'Lao' 'Vanuatu' 'Korea, Rep.'
 'New Zealand' 'Australia' 'Hong Kong, China' 'Singapore' 'Brunei'
 'Macao, China' 'Thailand' 'Samoa' 'Laos' 'Fiji' 'Taiwan' 'New Caledonia'
 'French Polynesia' 'Tonga' 'Micronesia, Fed. Sts.')
 then region='East Asia & Pacific';

if country in ('United Kingdom' 'France' 'Germany' 'Italy' 'Switzerland' 'Austria'
 'Belgium' 'Greece' 'Cyprus' 'Malta' 'Portugal' 'Czech Rep.' 'Poland' 'Slovak Republic'
 'Hungary' 'Estonia' 'Lithuania' 'Belarus' 'Russia' 'Kazakhstan' 'Turkmenistan' 'Ukraine'
 'Azerbaijan' 'Turkey' 'Serbia' 'Romania' 'Croatia' 'Moldova' 'Uzbekistan' 'Armenia'
 'Georgia' 'Albania' 'Bosnia and Herzegovina' 'Spain' 'Kyrgyzstan' 'Tajikistan'
 'Luxembourg' 'Slovenia' 'Denmark' 'Finland' 'Sweden' 'Ireland' 'Iceland' 'Latvia'
 'Bulgaria' 'Montenegro' 'Macedonia, FYR' 'Austria' 'Netherlands'
 'Norway'
 ) 
 then region='Europe & Central Asia';

if country in ('Qatar' 'Kuwait' 'United Arab Emirates' 'Israel' 'Bahrain' 'Oman'
 'Saudi Arabia' 'Iran' 'Lebanon' 'Tunisia' 'Algeria' 'Egypt' 'Syria' 'Libya'
 'Jordan' 'Morocco' 'Yemen, Rep.' 'Djibouti' 'Iraq' 'West Bank and Gaza') 
 then region='Middle East & North Africa';

if country in ('India' 'Afghanistan' 'Pakistan' 'Bangladesh' 'Nepal' 'Sri Lanka' 
 'Bhutan' 'Maldives') 
 then region='South Asia';
run;


/* save it into a permanent data set */
data here.gapminder_data; set gapminder_data;
run;