/*  Program for inserting updated/revised data  */
/*  It drops data in shredstg.PVLMM and appends it with revised version */

Options Symbolgen;
/*  Declare temporary library   */
%global tmplib;
%let tmplib=work;

/*  Macro for initializing empty datasets   */
%macro init(dset=);
proc sql;
    drop table &dset.;
quit;

data &dset.;
length Indicator $20
    Reporting_country $4
    reporting_country_label $40
    'EU / WB'n $3
    Week_Starting 8
    YEAR 8
    Month 8
    Week 8
    nat_orig $10
    Nationality_pers $10
    nat_pers_label $40
    Returning_country $10
    returning_country_label $40
    Ctry_return $10
    Failed_Asy $4
    Criminal_Sanction $4
    FacSus $4
    ProtReason $20
    Bus 8
    Van 8
    Car 8
    Airplane 8
    Other 8
    NA 8
    Region $40
    Total 8
    Stolen 8
    Lost 8
    PassengerType $10
    BorderSection $10
    'Entry/Exit'n $6
    Return_operation $16
    file $40
;
Indicator="QQQQQQQQQQQQQQQQQQQ";
Reporting_country="QQQQ";
reporting_country_label="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
'EU / WB'n="QQQ";
nat_orig="QQQQ";
Nationality_pers="QQQQQQQQQQ";
nat_pers_label="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
Returning_country="QQQQQQQQQQ";
returning_country_label="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
Ctry_return="QQQQQQQQQQ";
Failed_Asy="QQQQ";
Criminal_Sanction="QQQQ";
FacSUS="QQQQ";
ProtReason="QQQQQQQQQQQQQQQQQQQQ";
Region="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
PassengerType="QQQQQQQQQQ";
BorderSection="QQQQQQQQQQ";
'Entry/Exit'n="QQQQQQ";
Return_operation="QQQQQQQQQQQQQQQQ";
file="QQQQQQQQQQQQQQQQQQQQ";

format Indicator $20.
    Reporting_country $20.
    reporting_country_label $40.
    'EU / WB'n $3.
    Week_Starting DATE9.
    YEAR bestd5.
    Month bestd5.
    Week bestd5.
    nat_orig $4.
    Nationality_pers $4.
    nat_pers_label $40.
    Returning_country $4.
    returning_country_label $40.
    Ctry_return $10.
    Failed_Asy $4.
    Criminal_Sanction $4.
    FacSus $4.
    ProtReason $20.
    Bus bestd5.
    Van bestd5.
    Car bestd5.
    Airplane bestd5.
    Other bestd5.
    NA bestd5.
    Region $40.
    Total bestd14.
    Stolen bestd14.
    Lost bestd14.
    PassengerType $10.
    BorderSection $10.
    'Entry/Exit'n $6.
    Return_operation $16.
    file $40.
;
run;
%mend init;

/*  Clear all the datasets in WORK  */
proc datasets lib=work nolist nowarn kill memtype=(data view);
run;

proc sql noprint;
    drop table &tmplib..PVLMM_Data;
quit;

%init(dset=&tmplib..PVLMM_Data);

/*  Insert basic information    */
%macro basic_input(dset=);
    proc sql noprint;
        /*  Country ISOCode */
        select country
        into :ctry
        from &tmplib..ctry;
    quit;
    /*  Insert data */
    data &dset.;
    set &dset.;
        length country $4 file $40;
        Reporting_country="&ctry";
        file="&file";
    run;

%mend basic_input;
/*  Normalize nationalities */
%macro norm_nat(in=);
    data &in.(rename=(nat2=nat_orig));
    set &in.;
        nat2=strip(upcase(nat_orig));
        drop nat_orig;
    run;
%mend norm_nat;
/*  Macro for reading Excel tabs    */
%macro Import_One(file=);
    %put File for import: &file.;

    proc sql noprint;
        drop table &tmplib..ctry;
        drop table &tmplib..ASY1;
        drop table &tmplib..RET1_1;
        drop table &tmplib..RET1_2;
        drop table &tmplib..IBC1AOUT;
        drop table &tmplib..PASSPORT;
        drop table &tmplib..PASSFLOW;

        drop table &tmplib..ASY2;
        drop table &tmplib..RET2_1;
        drop table &tmplib..RET2F;
        drop table &tmplib..RET2V;
        drop table &tmplib..RET2R;
        drop table &tmplib..RET2;
        drop table &tmplib..IBC1AOut2;
        drop table &tmplib..Passport2;
        drop table &tmplib..Passport3;
        drop table &tmplib..PASSFLOW2;
    quit;

    data &tmplib..ctry;
        length country $4 file $40;
        file="&file";
        country=scan(file,1,"-");
        /*
        year=scan(file,3,"-")*1;
        */
        week_start=scan(file,4,"-")*1;
        week_end=scan(scan(file,5,"-"),1,".")*1;
    run;

    /*  Year    */
    /*
    proc import out=&tmplib..year
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="Instr & Def$C26:C26";
        usedate=yes;
        scantime=yes;
    run;

    proc contents data=&tmplib..year out=&tmplib..year2(keep=name) noprint;
    run;

    proc sql noprint;
    select name
    into :yrtmp
    from &tmplib..year2;
    quit;

    %global year;
    %let year=&yrtmp;*/
    /*  Asylum Inland   */
    proc import out=&tmplib..ASY1
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="1 Asylum Inland$A8:K300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Return  */
    proc import out=&tmplib..RET1_1
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="2 Return$A8:I300";
        usedate=yes;
        scantime=yes;
    run;

    proc import out=&tmplib..RET1_2
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="1 Return$A8:I300";
        usedate=yes;
        scantime=yes;
    run;

    /*  1A Out BCP  */
    proc import out=&tmplib..IBC1AOUT
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="1A Out BCP$A8:F300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Passport    */
    proc import out=&tmplib..PASSPORT
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="3 Passport$A8:F300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Passenger flow  */
    proc import out=&tmplib..PASSFLOW
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="2 Passenger flow$A8:E300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Check if tables exist   */
    proc sql noprint;
        select memname
        into :test1
        from sashelp.vmember
        where strip(upcase(memname))="ASY1";

        select memname
        into :test2
        from sashelp.vmember
        where strip(upcase(memname))="RET1_1";

        select memname
        into :test3
        from sashelp.vmember
        where strip(upcase(memname))="RET1_2";

        select memname
        into :test4
        from sashelp.vmember
        where strip(upcase(memname))="IBC1AOUT";

        select memname
        into :test5
        from sashelp.vmember
        where strip(upcase(memname))="PASSPORT";

        select memname
        into :test6
        from sashelp.vmember
        where strip(upcase(memname))="PASSFLOW";
    quit;
    /*  If Asylum tab exists then proceed   */
    %put &test1.;
    %if %symexist(test1) %then %do;
        data &tmplib..ASY2;
        set &tmplib..ASY1;
            where Totals*1 ne 0 and Totals*1 ne .;
            rename Nationality=nat_orig
                'Suspected Facilitation'n=FacSus
                'Stated Reason for Protection'n=ProtReas;
            Total=Totals*1;
            drop Totals Bus Van Car Airplane Other 'N.A.'n;
            length Indicator $20;
            Indicator="Asylum Inland";
            Bus1=Bus*1;
            Van1=Van*1;
            Car1=Car*1;
            Airplane1=Airplane*1;
            Other1=Other*1;
            NA='N.A.'n*1;
        run;

        data &tmplib..ASY2;
        set &tmplib..ASY2;
        rename Bus1=Bus Van1=Van Car1=Car Airplane1=Airplane Other1=Other;
        run;

        %basic_input(dset=&tmplib..ASY2);
        %norm_nat(in=&tmplib..ASY2);
    %end;
    /*  If Return tab exists then proceed   */
    %put &test2.;
    %if %symexist(test2) %then %do;
        data &tmplib..RET2_1;
        set &tmplib..RET1_1;
            where 'Total Returns'n*1 ne 0 and 'Total Returns'n*1 ne .;
            rename Nationality=nat_orig
                'Country of Return'n=Ctry_return
                'Returning Country'n=returning_country_label
                'Negative Asylum _Decision'n=Failed_Asy
                'Criminal Sanctions /_Extradition'n=Criminal_Sanction;
            Total='Total Returns'n*1;
            Forced='Total Forced'n*1;
            Voluntary='Total Voluntary'n*1;
            length Indicator $20 'Negative Asylum _Decision'n $4;
            if upcase(strip(F9)) eq "REFUSAL" then Indicator="Refusal";
            else Indicator="Return MS";
            drop 'Total Returns'n 'Total Forced'n 'Total Voluntary'n F9;
        run;

        %basic_input(dset=&tmplib..RET2_1);
        %norm_nat(in=&tmplib..RET2_1);

        data &tmplib..RET2F(drop=Total Voluntary) &tmplib..RET2V(drop=Total Forced);
        set &tmplib..RET2_1;
            where Indicator ne "Refusal";
        run;

        data &tmplib..RET2R(drop=Voluntary Forced);
        set &tmplib..RET2_1;
            where Indicator eq "Refusal" and Total ne 0;
        run;

        data &tmplib..RET2F;
        set &tmplib..RET2F;
            Return_operation="Forced";
            where Forced ne 0;
            rename Forced=Total;
        run;

        data &tmplib..RET2V;
        set &tmplib..RET2V;
            Return_operation="Voluntary";
            where Voluntary ne 0;
            rename Voluntary=Total;
        run;

        %init(dset=&tmplib..RET2);

        data &tmplib..RET2;
            if 0 then modify &tmplib..RET2;
            set &tmplib..RET2F open=defer;
            output;
        run;

        data &tmplib..RET2;
            if 0 then modify &tmplib..RET2;
            set &tmplib..RET2V open=defer;
            output;
        run;

        data &tmplib..RET2;
            if 0 then modify &tmplib..RET2;
            set &tmplib..RET2R open=defer;
            output;
        run;

        data &tmplib..RET2;
        set &tmplib..RET2;
            where Total ne . and Total ne 0;
        run;
        /*
        proc append base=&tmplib..RET2 data=&tmplib..RET2F;
        run;

        proc append base=&tmplib..RET2 data=&tmplib..RET2V;
        run;

        proc append base=&tmplib..RET2 data=&tmplib..RET2R;
        run;
        */
    %end;

    %put &test3.;
    %if %symexist(test3) %then %do;
        data &tmplib..RET2;
        set &tmplib..RET1_2;
            where 'Total Returns'n*1 ne 0 and 'Total Returns'n*1 ne .;
            rename Nationality=nat_orig
                'Country of Return'n=Ctry_return
                'Returning Country'n=returning_country_label
                'Negative Asylum _Decision'n=Failed_Asy
                'Criminal Sanctions /_Extradition'n=Criminal_Sanction;
            Total='Total Returns'n*1;
            Forced='Total Forced'n*1;
            Voluntary='Total Voluntary'n*1;
            length Indicator $20;
            if F9="refusal" then Indicator="Refusal";
            else Indicator="Return MS";
            drop 'Total Returns'n 'Total Forced'n 'Total Voluntary'n F9;
        run;

        %basic_input(dset=&tmplib..RET2);
        %norm_nat(in=&tmplib..RET2);
    %end;
    /* If 1A Out BCP tab exists then proceed */
    %put &test4.;
    %if %symexist(test4) %then %do;
        data &tmplib..IBC1AOut2;
        set &tmplib..IBC1AOut;
            where Totals*1 ne 0 and Totals*1 ne .;
            rename Nationality=nat_orig;
            Total=Totals*1;
            length Indicator $20;
            Indicator="ibc-1a";
            keep Week_Starting Nationality nat_orig Total Indicator;
        run;

        %basic_input(dset=&tmplib..IBC1AOut2);
        %norm_nat(in=&tmplib..IBC1AOut2);
    %end;
    /*  If Passport tab exists then proceed */
    %put &test5.;
    %if %symexist(test5) %then %do;
        data &tmplib..Passport2;
        set &tmplib..Passport;
            length Indicator $20;
            rename Nationality=nat_orig 'Administrative region'n=Region;
            Total='Total new requests'n*1;
            Lostn=lost*1;
            Stolenn=stolen*1;
            Indicator="Passport";
            where Week_Starting ne .;
            keep Week_Starting Nationality nat_orig Total Indicator 'Administrative region'n Region Lostn Stolenn;
        run;

        data &tmplib..Passport3;
        set &tmplib..Passport2;
            where (Total ne 0
            or Total ne .
            or Lostn ne 0
            or Lostn ne .
            or Stolenn ne 0
            or Stolenn ne .) and
            Week_Starting ne .;
            rename Lostn=Lost Stolenn=Stolen;
        run;

        %basic_input(dset=&tmplib..Passport3);
        %norm_nat(in=&tmplib..Passport3);
    %end;
    /*  If PassFlow tab exists then proceed */
    %put &test6.;
    %if %symexist(test6) %then %do;
        data &tmplib..PASSFLOW2;
        set &tmplib..PASSFLOW;
            length Indicator $20;
            where 'Totals'n*1 ne 0 and 'Totals'n*1 ne .;
            rename 'Type of passangers'n=PassengerType 'Border section'n=BorderSection;
            Total=Totals*1;
            Indicator="Passenger flow";
            keep Indicator Week_Starting PassengerType Total BorderSection 'Entry/Exit'n;
        run;

        %basic_input(dset=&tmplib..PassFlow2);
    %end;
    /*  Append tables   */
    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..ASY2 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..RET2 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..IBC1AOut2 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..Passport3 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..PassFlow2 open=defer;
        output;
    run;
%mend Import_One;

proc sql;
    drop table &tmplib..PVLMM_Data3;
    drop table &tmplib..PVLMM_Data4;
    drop table &tmplib..Base_countries;
    drop table &tmplib..PVLMM_Data5;
    drop table &tmplib..nat_dictionary;
    drop table &tmplib..PVLMM_Data6;
    drop table &tmplib..nat_labels;
    drop table &tmplib..PVLMM_Data7;
    drop table &tmplib..PVLMM_Data_Final;
    drop table &tmplib..PVLMM_Data_Final2;
    drop table &tmplib..PVLMM_Data_Final3;
quit;

/********************************************/
/*  Choose year !!!!!!!!!!!!!!!!!!!!!!!!!!  */
/********************************************/
%global year;
%let year=2014;
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
/*  Change the folder name here !!!!!!!!!   */
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
%global path2;
%let path2=14 May 2014;
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
/*  Select file for update  */
%global file_upd;
%let file_upd=SVN-WBMM-2014-14-17-v1-1.xls;
/********************************************/
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
%global path1;
%let path1 = \\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\REPORTING\TOOLS\PVLMM\&year.;
%global root;
%let root=&path1.\&path2;
%put Chosen path: &root.;

%Import_One(file=&file_upd);

/*  All post-import operations  */

proc sql;
    create table &tmplib..PVLMM_Data3 as
    select distinct Indicator,Reporting_country,reporting_country_label,'EU / WB'n,Week_Starting,YEAR,Month,Week,nat_orig,Nationality_pers,nat_pers_label,Returning_country,returning_country_label,Ctry_return,Failed_Asy,Criminal_Sanction,FacSus,ProtReason,sum(Bus) as Bus,sum (Van) as Van,sum(Car) as Car,sum(Airplane) as Airplane,sum(Other) as Other,sum(NA) as NA,Region,sum(Total) as Total,Stolen,Lost,PassengerType,BorderSection,'Entry/Exit'n,Return_operation,file
    from &tmplib..PVLMM_Data
    group by Indicator,Reporting_country,reporting_country_label,'EU / WB'n,Week_Starting,YEAR,Month,Week,nat_orig,Nationality_pers,nat_pers_label,Returning_country,returning_country_label,Ctry_return,Failed_Asy,Criminal_Sanction,FacSus,ProtReason,Region,Stolen,Lost,PassengerType,BorderSection,'Entry/Exit'n,Return_operation,file;
quit;

data &tmplib..PVLMM_Data4;
set &tmplib..PVLMM_Data3;
    where Indicator ne "QQQQQQQQQQQQQQQQQQQ" and file="&file_upd";
    if Reporting_country="GER" then Reporting_country="DEU";
    if Reporting_country="GER" then Reporting_country_label="Germany";
    if Reporting_country="DEU" then Reporting_country_label="Germany";
    if Nationality_pers="" and Indicator ne "" then do;
        Nationality_pers="UNK";
        nat_pers_label="Unknown";
    end;
run;

/********************************************/
/*  Import Isocode dictionary - BruteForce  */
/********************************************/

%put ________Import Isocode dictionary________;

DATA &tmplib..Base_countries;
    LENGTH
        id                 8
        name             $ 44
        capname          $ 44
        frename          $ 44
        capfrename       $ 44
        iso2code         $ 2
        iso3code         $ 3
        eu                 8
        regionid           8
        subregionid        8
        visa               8
        regionname       $ 10
        subregionname    $ 25
        schengen           8 ;
    FORMAT
        id               BEST12.
        name             $CHAR44.
        capname          $CHAR44.
        frename          $CHAR44.
        capfrename       $CHAR44.
        iso2code         $CHAR2.
        iso3code         $CHAR3.
        eu               BEST12.
        regionid         BEST12.
        subregionid      BEST12.
        visa             BEST12.
        regionname       $CHAR10.
        subregionname    $CHAR25.
        schengen         BEST12. ;
    INFORMAT
        id               BEST12.
        name             $CHAR44.
        capname          $CHAR44.
        frename          $CHAR44.
        capfrename       $CHAR44.
        iso2code         $CHAR2.
        iso3code         $CHAR3.
        eu               BEST12.
        regionid         BEST12.
        subregionid      BEST12.
        visa             BEST12.
        regionname       $CHAR10.
        subregionname    $CHAR25.
        schengen         BEST12. ;
    INFILE DATALINES4
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        id               : BEST32.
        name             : $CHAR44.
        capname          : $CHAR44.
        frename          : $CHAR44.
        capfrename       : $CHAR44.
        iso2code         : $CHAR2.
        iso3code         : $CHAR3.
        eu               : BEST32.
        regionid         : BEST32.
        subregionid      : BEST32.
        visa             : BEST32.
        regionname       : $CHAR10.
        subregionname    : $CHAR25.
        schengen         : BEST32. ;
DATALINES4;
101AfghanistanAFGHANISTANAfghanistanAFGHANISTANAFAFG05046191AsiaSouthern Asia0
102Aland IslandsÅLAND ISLANDSÅland, ÎlesÅLAND, ÎLESAXALA05056140EuropeNorthern Europe0
103AlbaniaALBANIAAlbanieALBANIEALALB05056201EuropeSouthern Europe0
104AlgeriaALGERIAAlgérieALGÉRIEDZDZA05016121AfricaNorthern Africa0
105American SamoaAMERICAN SAMOASamoa AméricainesSAMOA AMÉRICAINESASASM05066151OceaniaPolynesia0
106AndorraANDORRAAndorreANDORREADAND05056200EuropeSouthern Europe0
107AngolaANGOLAAngolaANGOLAAOAGO05016111AfricaMiddle Africa0
108AnguillaANGUILLAAnguillaANGUILLAAIAIA05026030AmericaCaribbean0
109AntarcticaANTARCTICAAntarctiqueANTARCTIQUEAQATA05036010AntarcticaAntarctica0
110Antigua and BarbudaANTIGUA AND BARBUDAAntigua-Et-BarbudaANTIGUA-ET-BARBUDAAGATG05026030AmericaCaribbean0
111ArgentinaARGENTINAArgentineARGENTINEARARG05026160AmericaSouth America0
112ArmeniaARMENIAArménieARMÉNIEAMARM05046221AsiaWestern Asia0
113ArubaARUBAArubaARUBAAWABW05026030AmericaCaribbean0
114AustraliaAUSTRALIAAustralieAUSTRALIEAUAUS05066020OceaniaAustralia and New Zealand0
115AustriaAUSTRIAAutricheAUTRICHEATAUT15056230EuropeWestern Europe1
116AzerbaijanAZERBAIJANAzerbaïdjanAZERBAÏDJANAZAZE05046221AsiaWestern Asia0
117BahamasBAHAMASBahamasBAHAMASBSBHS05026030AmericaCaribbean0
118BahrainBAHRAINBahreïnBAHREÏNBHBHR05046221AsiaWestern Asia0
119BangladeshBANGLADESHBangladeshBANGLADESHBDBGD05046191AsiaSouthern Asia0
120BarbadosBARBADOSBarbadeBARBADEBBBRB05026030AmericaCaribbean0
121BelarusBELARUSBélarusBÉLARUSBYBLR05056081EuropeEastern Europe0
122BelgiumBELGIUMBelgiqueBELGIQUEBEBEL15056230EuropeWestern Europe1
123BelizeBELIZEBelizeBELIZEBZBLZ05026041AmericaCentral America0
124BeninBENINBéninBÉNINBJBEN05016211AfricaWestern Africa0
125BermudaBERMUDABermudesBERMUDESBMBMU05026130AmericaNorth America0
126BhutanBHUTANBhoutanBHOUTANBTBTN05046191AsiaSouthern Asia0
127BoliviaBOLIVIA, PLURINATIONAL STATE OFBolivie, L'État Plurinational DeBOLIVIE, l'ÉTAT PLURINATIONAL DEBOBOL05026161AmericaSouth America0
128Bosnia and HerzegovinaBOSNIA AND HERZEGOVINABosnie-HerzégovineBOSNIE-HERZÉGOVINEBABIH05056201EuropeSouthern Europe0
129BotswanaBOTSWANABotswanaBOTSWANABWBWA05016181AfricaSouthern Africa0
130Bouvet IslandBOUVET ISLANDBouvet, ÎleBOUVET, ÎLEBVBVT05056140EuropeNorthern Europe0
131BrazilBRAZILBrésilBRÉSILBRBRA05026160AmericaSouth America0
132British Indian Ocean TerritoryBRITISH INDIAN OCEAN TERRITORYOcéan Indien, Territoire Britannique De L'OCÉAN INDIEN, TERRITOIRE BRITANNIQUE DE L'IOIOT05026030AmericaCaribbean0
133Brunei DarussalamBRUNEI DARUSSALAMBrunéi DarussalamBRUNÉI DARUSSALAMBNBRN05046170AsiaSouth-Eastern Asia0
134BulgariaBULGARIABulgarieBULGARIEBGBGR15056080EuropeEastern Europe0
135Burkina FasoBURKINA FASOBurkina FasoBURKINA FASOBFBFA05016211AfricaWestern Africa0
136BurundiBURUNDIBurundiBURUNDIBIBDI05016061AfricaEastern Africa0
137CambodiaCAMBODIACambodgeCAMBODGEKHKHM05046171AsiaSouth-Eastern Asia0
138CameroonCAMEROONCamerounCAMEROUNCMCMR05016111AfricaMiddle Africa0
139CanadaCANADACanadaCANADACACAN05026130AmericaNorth America0
140Cape VerdeCAPE VERDECap-VertCAP-VERTCVCPV05016211AfricaWestern Africa0
141Cayman IslandsCAYMAN ISLANDSCaïmanes, ÎlesCAÏMANES, ÎLESKYCYM05026031AmericaCaribbean0
142Central African RepublicCENTRAL AFRICAN REPUBLICCentrafricaine, RépubliqueCENTRAFRICAINE, RÉPUBLIQUECFCAF05016111AfricaMiddle Africa0
143ChadCHADTchadTCHADTDTCD05016111AfricaMiddle Africa0
144ChileCHILEChiliCHILICLCHL05026160AmericaSouth America0
145ChinaCHINAChineCHINECNCHN05046071AsiaEastern Asia0
146Christmas IslandCHRISTMAS ISLANDChristmas, ÎleCHRISTMAS, ÎLECXCXR05066020OceaniaAustralia and New Zealand0
147Cocos (Keeling) IslandsCOCOS (KEELING) ISLANDSCocos (Keeling), ÎlesCOCOS (KEELING), ÎLESCCCCK05066020OceaniaAustralia and New Zealand0
148ColombiaCOLOMBIAColombieCOLOMBIECOCOL05026161AmericaSouth America0
149ComorosCOMOROSComoresCOMORESKMCOM05016061AfricaEastern Africa0
150CongoCONGOCongoCONGOCGCOG05016111AfricaMiddle Africa0
151Congo, the Democratic Republic of theCONGO, THE DEMOCRATIC REPUBLIC OF THECongo, La République Démocratique DuCONGO, LA RÉPUBLIQUE DÉMOCRATIQUE DUCDCOD05016111AfricaMiddle Africa0
152Cook IslandsCOOK ISLANDSCook, ÎlesCOOK, ÎLESCKCOK05066150OceaniaPolynesia0
153Costa RicaCOSTA RICACosta RicaCOSTA RICACRCRI05026040AmericaCentral America0
154Côte d'IvoireCÔTE D'IVOIRECôte D'IvoireCÔTE D'IVOIRECICIV05016211AfricaWestern Africa0
155CroatiaCROATIACroatieCROATIEHRHRV05056200EuropeSouthern Europe0
156CubaCUBACubaCUBACUCUB05026031AmericaCaribbean0
157CyprusCYPRUSChypreCHYPRECYCYP15056200EuropeSouthern Europe0
158Czech RepublicCZECH REPUBLICTchèque, RépubliqueTCHÈQUE, RÉPUBLIQUECZCZE15056080EuropeEastern Europe1
159DenmarkDENMARKDanemarkDANEMARKDKDNK15056140EuropeNorthern Europe1
160DjiboutiDJIBOUTIDjiboutiDJIBOUTIDJDJI05016061AfricaEastern Africa0
161DominicaDOMINICADominiqueDOMINIQUEDMDMA05026031AmericaCaribbean0
162Dominican RepublicDOMINICAN REPUBLICDominicaine, RépubliqueDOMINICAINE, RÉPUBLIQUEDODOM05026031AmericaCaribbean0
163EcuadorECUADORÉquateurÉQUATEURECECU05026161AmericaSouth America0
164EgyptEGYPTÉgypteÉGYPTEEGEGY05016121AfricaNorthern Africa0
165El SalvadorEL SALVADOREl SalvadorEL SALVADORSVSLV05026040AmericaCentral America0
166Equatorial GuineaEQUATORIAL GUINEAGuinée ÉquatorialeGUINÉE ÉQUATORIALEGQGNQ05016111AfricaMiddle Africa0
167EritreaERITREAÉrythréeÉRYTHRÉEERERI05016061AfricaEastern Africa0
168EstoniaESTONIAEstonieESTONIEEEEST15056140EuropeNorthern Europe1
169EthiopiaETHIOPIAÉthiopieÉTHIOPIEETETH05016060AfricaEastern Africa0
170Falkland Islands (Malvinas)FALKLAND ISLANDS (MALVINAS)Falkland, Îles (Malvinas)FALKLAND, ÎLES (MALVINAS)FKFLK05026161AmericaSouth America0
171Faroe IslandsFAROE ISLANDSFéroé, ÎlesFÉROÉ, ÎLESFOFRO05056140EuropeNorthern Europe0
172FijiFIJIFidjiFIDJIFJFJI05066091OceaniaMelanesia0
173FinlandFINLANDFinlandeFINLANDEFIFIN15056140EuropeNorthern Europe1
174FranceFRANCEFranceFRANCEFRFRA15056230EuropeWestern Europe1
175French GuianaFRENCH GUIANAGuyane FrançaiseGUYANE FRANÇAISEGFGUF05026160AmericaSouth America0
176French PolynesiaFRENCH POLYNESIAPolynésie FrançaisePOLYNÉSIE FRANÇAISEPFPYF05066150OceaniaPolynesia0
177French Southern TerritoriesFRENCH SOUTHERN TERRITORIESTerres Australes FrançaisesTERRES AUSTRALES FRANÇAISESTFATF05056230EuropeWestern Europe0
178GabonGABONGabonGABONGAGAB05016111AfricaMiddle Africa0
179GambiaGAMBIAGambieGAMBIEGMGMB05016211AfricaWestern Africa0
180GeorgiaGEORGIAGéorgieGÉORGIEGEGEO05046221AsiaWestern Asia0
181GermanyGERMANYAllemagneALLEMAGNEDEDEU15056230EuropeWestern Europe1
182GhanaGHANAGhanaGHANAGHGHA05016211AfricaWestern Africa0
183GibraltarGIBRALTARGibraltarGIBRALTARGIGIB05056200EuropeSouthern Europe0
184GreeceGREECEGrèceGRÈCEGRGRC15056200EuropeSouthern Europe1
185GreenlandGREENLANDGroenlandGROENLANDGLGRL05026130AmericaNorth America0
186GrenadaGRENADAGrenadeGRENADEGDGRD05026031AmericaCaribbean0
187GuadeloupeGUADELOUPEGuadeloupeGUADELOUPEGPGLP05026030AmericaCaribbean0
188GuamGUAMGuamGUAMGUGUM05066100OceaniaMicronesia0
189GuatemalaGUATEMALAGuatemalaGUATEMALAGTGTM05026040AmericaCentral America0
190GuernseyGUERNSEYGuerneseyGUERNESEYGGGGY05056140EuropeNorthern Europe0
191GuineaGUINEAGuinéeGUINÉEGNGIN05016211AfricaWestern Africa0
192Guinea-BissauGUINEA-BISSAUGuinée-BissauGUINÉE-BISSAUGWGNB05016211AfricaWestern Africa0
193GuyanaGUYANAGuyanaGUYANAGYGUY05026161AmericaSouth America0
194HaitiHAITIHaïtiHAÏTIHTHTI05026031AmericaCaribbean0
195Heard Island and McDonald IslandsHEARD ISLAND AND MCDONALD ISLANDSHeard, Île Et Mcdonald, ÎlesHEARD, ÎLE ET MCDONALD, ÎLESHMHMD05066020OceaniaAustralia and New Zealand0
196Holy See (Vatican City State)HOLY SEE (VATICAN CITY STATE)Saint-Siège (État De La Cité Du Vatican)SAINT-SIÈGE (ÉTAT DE LA CITÉ DU VATICAN)VAVAT05056200EuropeSouthern Europe0
197HondurasHONDURASHondurasHONDURASHNHND05026040AmericaCentral America0
198Hong KongHONG KONGHong-KongHONG-KONGHKHKG05046070AsiaEastern Asia0
199HungaryHUNGARYHongrieHONGRIEHUHUN15056080EuropeEastern Europe1
200IcelandICELANDIslandeISLANDEISISL05056140EuropeNorthern Europe1
201IndiaINDIAIndeINDEININD05046191AsiaSouthern Asia0
202IndonesiaINDONESIAIndonésieINDONÉSIEIDIDN05046171AsiaSouth-Eastern Asia0
203Iran, Islamic Republic ofIRAN, ISLAMIC REPUBLIC OFIran, République Islamique D'IRAN, RÉPUBLIQUE ISLAMIQUE D'IRIRN05046191AsiaSouthern Asia0
204IraqIRAQIraqIRAQIQIRQ05046221AsiaWestern Asia0
205IrelandIRELANDIrlandeIRLANDEIEIRL15056140EuropeNorthern Europe0
206Isle of ManISLE OF MANÎle De ManÎLE DE MANIMIMN05056140EuropeNorthern Europe0
207IsraelISRAELIsraëlISRAËLILISR05046220AsiaWestern Asia0
208ItalyITALYItalieITALIEITITA15056200EuropeSouthern Europe1
209JamaicaJAMAICAJamaïqueJAMAÏQUEJMJAM05026031AmericaCaribbean0
210JapanJAPANJaponJAPONJPJPN05046070AsiaEastern Asia0
211JerseyJERSEYJerseyJERSEYJEJEY05056140EuropeNorthern Europe0
212JordanJORDANJordanieJORDANIEJOJOR05046221AsiaWestern Asia0
213KazakhstanKAZAKHSTANKazakhstanKAZAKHSTANKZKAZ05046051AsiaCentral Asia0
214KenyaKENYAKenyaKENYAKEKEN05016061AfricaEastern Africa0
215KiribatiKIRIBATIKiribatiKIRIBATIKIKIR05066101OceaniaMicronesia0
216Korea, Democratic People's Republic ofKOREA, DEMOCRATIC PEOPLE'S REPUBLIC OFCorée, République Populaire Démocratique DeCORÉE, RÉPUBLIQUE POPULAIRE DÉMOCRATIQUE DEKPPRK05046071AsiaEastern Asia0
217Korea, Republic ofKOREA, REPUBLIC OFCorée, République DeCORÉE, RÉPUBLIQUE DEKRKOR05046070AsiaEastern Asia0
218KuwaitKUWAITKoweïtKOWEÏTKWKWT05046221AsiaWestern Asia0
219KyrgyzstanKYRGYZSTANKirghizistanKIRGHIZISTANKGKGZ05046051AsiaCentral Asia0
220Lao People's Democratic RepublicLAO PEOPLE'S DEMOCRATIC REPUBLICLao, République Démocratique PopulaireLAO, RÉPUBLIQUE DÉMOCRATIQUE POPULAIRELALAO05046171AsiaSouth-Eastern Asia0
221LatviaLATVIALettonieLETTONIELVLVA15056140EuropeNorthern Europe1
222LebanonLEBANONLibanLIBANLBLBN05046221AsiaWestern Asia0
223LesothoLESOTHOLesothoLESOTHOLSLSO05016181AfricaSouthern Africa0
224LiberiaLIBERIALibériaLIBÉRIALRLBR05016211AfricaWestern Africa0
225Libyan Arab JamahiriyaLIBYAN ARAB JAMAHIRIYALibyenne, Jamahiriya ArabeLIBYENNE, JAMAHIRIYA ARABELYLBY05016121AfricaNorthern Africa0
226LiechtensteinLIECHTENSTEINLiechtensteinLIECHTENSTEINLILIE05056230EuropeWestern Europe0
227LithuaniaLITHUANIALituanieLITUANIELTLTU15056140EuropeNorthern Europe1
228LuxembourgLUXEMBOURGLuxembourgLUXEMBOURGLULUX15056230EuropeWestern Europe1
229MacaoMACAOMacaoMACAOMOMAC05046070AsiaEastern Asia0
230FYROMMACEDONIA, THE FORMER YUGOSLAV REPUBLIC OFMacédoine, L'Ex-République Yougoslave DeMACÉDOINE, L'EX-RÉPUBLIQUE YOUGOSLAVE DEMKMKD05056201EuropeSouthern Europe0
231MadagascarMADAGASCARMadagascarMADAGASCARMGMDG05016061AfricaEastern Africa0
232MalawiMALAWIMalawiMALAWIMWMWI05016061AfricaEastern Africa0
233MalaysiaMALAYSIAMalaisieMALAISIEMYMYS05046170AsiaSouth-Eastern Asia0
234MaldivesMALDIVESMaldivesMALDIVESMVMDV05046191AsiaSouthern Asia0
235MaliMALIMaliMALIMLMLI05016211AfricaWestern Africa0
236MaltaMALTAMalteMALTEMTMLT15056200EuropeSouthern Europe1
237Marshall IslandsMARSHALL ISLANDSMarshall, ÎlesMARSHALL, ÎLESMHMHL05066101OceaniaMicronesia0
238MartiniqueMARTINIQUEMartiniqueMARTINIQUEMQMTQ05026030AmericaCaribbean0
239MauritaniaMAURITANIAMauritanieMAURITANIEMRMRT05016211AfricaWestern Africa0
240MauritiusMAURITIUSMauriceMAURICEMUMUS05016060AfricaEastern Africa0
241MayotteMAYOTTEMayotteMAYOTTEYTMYT05016060AfricaEastern Africa0
242MexicoMEXICOMexiqueMEXIQUEMXMEX05026040AmericaCentral America0
243Micronesia, Federated States ofMICRONESIA, FEDERATED STATES OFMicronésie, États Fédérés DeMICRONÉSIE, ÉTATS FÉDÉRÉS DEFMFSM05066101OceaniaMicronesia0
244MoldovaMOLDOVA, REPUBLIC OFMoldova, République DeMOLDOVA, RÉPUBLIQUE DEMDMDA05056081EuropeEastern Europe0
245MonacoMONACOMonacoMONACOMCMCO05056230EuropeWestern Europe0
246MongoliaMONGOLIAMongolieMONGOLIEMNMNG05046071AsiaEastern Asia0
247MontenegroMONTENEGROMonténégroMONTÉNÉGROMEMNE05056201EuropeSouthern Europe0
248MontserratMONTSERRATMontserratMONTSERRATMSMSR05026031AmericaCaribbean0
249MoroccoMOROCCOMarocMAROCMAMAR05016121AfricaNorthern Africa0
250MozambiqueMOZAMBIQUEMozambiqueMOZAMBIQUEMZMOZ05016061AfricaEastern Africa0
251MyanmarMYANMARMyanmarMYANMARMMMMR05046171AsiaSouth-Eastern Asia0
252NamibiaNAMIBIANamibieNAMIBIENANAM05016181AfricaSouthern Africa0
253NauruNAURUNauruNAURUNRNRU05066101OceaniaMicronesia0
254NepalNEPALNépalNÉPALNPNPL05046191AsiaSouthern Asia0
255NetherlandsNETHERLANDSPays-BasPAYS-BASNLNLD15056230EuropeWestern Europe1
256Netherlands AntillesNETHERLANDS ANTILLESAntilles NéerlandaisesANTILLES NÉERLANDAISESANANT05026030AmericaCaribbean0
257New CaledoniaNEW CALEDONIANouvelle-CalédonieNOUVELLE-CALÉDONIENCNCL05066090OceaniaMelanesia0
258New ZealandNEW ZEALANDNouvelle-ZélandeNOUVELLE-ZÉLANDENZNZL05066020OceaniaAustralia and New Zealand0
259NicaraguaNICARAGUANicaraguaNICARAGUANINIC05026040AmericaCentral America0
260NigerNIGERNigerNIGERNENER05016211AfricaWestern Africa0
261NigeriaNIGERIANigériaNIGÉRIANGNGA05016211AfricaWestern Africa0
262NiueNIUENiuéNIUÉNUNIU05066150OceaniaPolynesia0
263Norfolk IslandNORFOLK ISLANDNorfolk, ÎleNORFOLK, ÎLENFNFK05066020OceaniaAustralia and New Zealand0
264Northern Mariana IslandsNORTHERN MARIANA ISLANDSMariannes Du Nord, ÎlesMARIANNES DU NORD, ÎLESMPMNP05066101OceaniaMicronesia0
265NorwayNORWAYNorvègeNORVÈGENONOR05056140EuropeNorthern Europe1
266OmanOMANOmanOMANOMOMN05046221AsiaWestern Asia0
267PakistanPAKISTANPakistanPAKISTANPKPAK05046191AsiaSouthern Asia0
268PalauPALAUPalaosPALAOSPWPLW05066100OceaniaMicronesia0
269PalestinePALESTINIAN TERRITORY, OCCUPIEDPalestinien Occupé, TerritoirePALESTINIEN OCCUPÉ, TERRITOIREPSPSE05046220AsiaWestern Asia0
270PanamaPANAMAPanamaPANAMAPAPAN05026040AmericaCentral America0
271Papua New GuineaPAPUA NEW GUINEAPapouasie-Nouvelle-GuinéePAPOUASIE-NOUVELLE-GUINÉEPGPNG05066091OceaniaMelanesia0
272ParaguayPARAGUAYParaguayPARAGUAYPYPRY05026160AmericaSouth America0
273PeruPERUPérouPÉROUPEPER05026161AmericaSouth America0
274PhilippinesPHILIPPINESPhilippinesPHILIPPINESPHPHL05046171AsiaSouth-Eastern Asia0
275PitcairnPITCAIRNPitcairnPITCAIRNPNPCN05066151OceaniaPolynesia0
276PolandPOLANDPolognePOLOGNEPLPOL15056080EuropeEastern Europe1
277PortugalPORTUGALPortugalPORTUGALPTPRT15056200EuropeSouthern Europe1
278Puerto RicoPUERTO RICOPorto RicoPORTO RICOPRPRI05026030AmericaCaribbean0
279QatarQATARQatarQATARQAQAT05046221AsiaWestern Asia0
280RéunionRÉUNIONRéunionRÉUNIONREREU05016060AfricaEastern Africa0
281RomaniaROMANIARoumanieROUMANIEROROU15056080EuropeEastern Europe0
282RussiaRUSSIAN FEDERATIONRussie, Fédération DeRUSSIE, FÉDÉRATION DERURUS05056081EuropeEastern Europe0
283RwandaRWANDARwandaRWANDARWRWA05016061AfricaEastern Africa0
284Saint BarthélemySAINT BARTHÉLEMYSaint-BarthélemySAINT-BARTHÉLEMYBLBLM05026030AmericaCaribbean0
285Saint HelenaSAINT HELENA, ASCENSION AND TRISTAN DA CUNHASainte-Hélène, Ascension Et Tristan Da CunhaSAINTE-HÉLÈNE, ASCENSION ET TRISTAN DA CUNHASHSHN05016211AfricaWestern Africa0
286Saint Kitts and NevisSAINT KITTS AND NEVISSaint-Kitts-Et-NevisSAINT-KITTS-ET-NEVISKNKNA05026030AmericaCaribbean0
287Saint LuciaSAINT LUCIASainte-LucieSAINTE-LUCIELCLCA05026031AmericaCaribbean0
288Saint MartinSAINT MARTINSaint-MartinSAINT-MARTINMFMAF05026030AmericaCaribbean0
289Saint Pierre and MiquelonSAINT PIERRE AND MIQUELONSaint-Pierre-Et-MiquelonSAINT-PIERRE-ET-MIQUELONPMSPM05026130AmericaNorth America0
290Saint Vincent and the GrenadinesSAINT VINCENT AND THE GRENADINESSaint-Vincent-Et-Les GrenadinesSAINT-VINCENT-ET-LES GRENADINESVCVCT05026031AmericaCaribbean0
291SamoaSAMOASamoaSAMOAWSWSM05066151OceaniaPolynesia0
292San MarinoSAN MARINOSaint-MarinSAINT-MARINSMSMR05056200EuropeSouthern Europe0
293Sao Tome and Principe[1]SAO TOME AND PRINCIPESao Tomé-Et-PrincipeSAO TOMÉ-ET-PRINCIPESTSTP05016111AfricaMiddle Africa0
294Saudi ArabiaSAUDI ARABIAArabie SaouditeARABIE SAOUDITESASAU05046221AsiaWestern Asia0
295SenegalSENEGALSénégalSÉNÉGALSNSEN05016211AfricaWestern Africa0
296SerbiaSERBIASerbieSERBIERSSRB05056201EuropeSouthern Europe0
297SeychellesSEYCHELLESSeychellesSEYCHELLESSCSYC05016060AfricaEastern Africa0
298Sierra LeoneSIERRA LEONESierra LeoneSIERRA LEONESLSLE05016211AfricaWestern Africa0
299SingaporeSINGAPORESingapourSINGAPOURSGSGP05046170AsiaSouth-Eastern Asia0
300SlovakiaSLOVAKIASlovaquieSLOVAQUIESKSVK15056080EuropeEastern Europe1
301SloveniaSLOVENIASlovénieSLOVÉNIESISVN15056200EuropeSouthern Europe1
302Solomon IslandsSOLOMON ISLANDSSalomon, ÎlesSALOMON, ÎLESSBSLB05066091OceaniaMelanesia0
303SomaliaSOMALIASomalieSOMALIESOSOM05016061AfricaEastern Africa0
304South AfricaSOUTH AFRICAAfrique Du SudAFRIQUE DU SUDZAZAF05016181AfricaSouthern Africa0
305South Georgia and the South Sandwich IslandsSOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDSGéorgie Du Sud Et Les Îles Sandwich Du SudGÉORGIE DU SUD ET LES ÎLES SANDWICH DU SUDGSSGS05056140EuropeNorthern Europe0
306SpainSPAINEspagneESPAGNEESESP15056200EuropeSouthern Europe1
307Sri LankaSRI LANKASri LankaSRI LANKALKLKA05046191AsiaSouthern Asia0
308SudanSUDANSoudanSOUDANSDSDN05016121AfricaNorthern Africa0
309SurinameSURINAMESurinameSURINAMESRSUR05026161AmericaSouth America0
310Svalbard and Jan MayenSVALBARD AND JAN MAYENSvalbard Et Île Jan MayenSVALBARD ET ÎLE JAN MAYENSJSJM05056140EuropeNorthern Europe0
311SwazilandSWAZILANDSwazilandSWAZILANDSZSWZ05016181AfricaSouthern Africa0
312SwedenSWEDENSuèdeSUÈDESESWE15056140EuropeNorthern Europe1
313SwitzerlandSWITZERLANDSuisseSUISSECHCHE05056230EuropeWestern Europe1
314SyriaSYRIAN ARAB REPUBLICSyrienne, République ArabeSYRIENNE, RÉPUBLIQUE ARABESYSYR05046221AsiaWestern Asia0
315Taiwan, Province of ChinaTAIWAN, PROVINCE OF CHINATaïwan, Province De ChineTAÏWAN, PROVINCE DE CHINETWTWN05046071AsiaEastern Asia0
316TajikistanTAJIKISTANTadjikistanTADJIKISTANTJTJK05046051AsiaCentral Asia0
317Tanzania, United Republic ofTANZANIA, UNITED REPUBLIC OFTanzanie, République-Unie DeTANZANIE, RÉPUBLIQUE-UNIE DETZTZA05016061AfricaEastern Africa0
318ThailandTHAILANDThaïlandeTHAÏLANDETHTHA05046171AsiaSouth-Eastern Asia0
319Timor-LesteTIMOR-LESTETimor-LesteTIMOR-LESTETLTLS05046171AsiaSouth-Eastern Asia0
320TogoTOGOTogoTOGOTGTGO05016211AfricaWestern Africa0
321TokelauTOKELAUTokelauTOKELAUTKTKL05066150OceaniaPolynesia0
322TongaTONGATongaTONGATOTON05066151OceaniaPolynesia0
323Trinidad and TobagoTRINIDAD AND TOBAGOTrinité-Et-TobagoTRINITÉ-ET-TOBAGOTTTTO05026031AmericaCaribbean0
324TunisiaTUNISIATunisieTUNISIETNTUN05016121AfricaNorthern Africa0
325TurkeyTURKEYTurquieTURQUIETRTUR05046221AsiaWestern Asia0
326TurkmenistanTURKMENISTANTurkménistanTURKMÉNISTANTMTKM05046051AsiaCentral Asia0
327Turks and Caicos IslandsTURKS AND CAICOS ISLANDSTurks Et Caïques, ÎlesTURKS ET CAÏQUES, ÎLESTCTCA05026031AmericaCaribbean0
328TuvaluTUVALUTuvaluTUVALUTVTUV05066151OceaniaPolynesia0
329UgandaUGANDAOugandaOUGANDAUGUGA05016061AfricaEastern Africa0
330UkraineUKRAINEUkraineUKRAINEUAUKR05056081EuropeEastern Europe0
331United Arab EmiratesUNITED ARAB EMIRATESÉmirats Arabes UnisÉMIRATS ARABES UNISAEARE05046221AsiaWestern Asia0
332United KingdomUNITED KINGDOMRoyaume-UniROYAUME-UNIGBGBR15056140EuropeNorthern Europe0
333United StatesUNITED STATESÉtats-UnisÉTATS-UNISUSUSA05026130AmericaNorth America0
334United States Minor Outlying IslandsUNITED STATES MINOR OUTLYING ISLANDSÎles Mineures Éloignées Des États-UnisÎLES MINEURES ÉLOIGNÉES DES ÉTATS-UNISUMUMI05026130AmericaNorth America0
335UruguayURUGUAYUruguayURUGUAYUYURY05026160AmericaSouth America0
336UzbekistanUZBEKISTANOuzbékistanOUZBÉKISTANUZUZB05046051AsiaCentral Asia0
337VanuatuVANUATUVanuatuVANUATUVUVUT05066091OceaniaMelanesia0
338VenezuelaVENEZUELA, BOLIVARIAN REPUBLIC OFVenezuela, République Bolivarienne DuVENEZUELA, RÉPUBLIQUE BOLIVARIENNE DUVEVEN05026160AmericaSouth America0
339Viet NamVIET NAMViet NamVIET NAMVNVNM05046171AsiaSouth-Eastern Asia0
340Virgin Islands, BritishVIRGIN ISLANDS, BRITISHÎles Vierges BritanniquesÎLES VIERGES BRITANNIQUESVGVGB05056140EuropeNorthern Europe0
341Virgin Islands, U.S.VIRGIN ISLANDS, U.S.Îles Vierges Des États-UnisÎLES VIERGES DES ÉTATS-UNISVIVIR05026031AmericaCaribbean0
342Wallis and FutunaWALLIS AND FUTUNAWallis Et FutunaWALLIS ET FUTUNAWFWLF05066150OceaniaPolynesia0
343Western SaharaWESTERN SAHARASahara OccidentalSAHARA OCCIDENTALEHESH05016120AfricaNorthern Africa0
344YemenYEMENYémenYÉMENYEYEM05046221AsiaWestern Asia0
345ZambiaZAMBIAZambieZAMBIEZMZMB05016061AfricaEastern Africa0
346ZimbabweZIMBABWEZimbabweZIMBABWEZWZWE05016061AfricaEastern Africa0
347Other and statelessOTHER AND STATELESSAutres et sans-etatsAUTRES ET SANS-ETATS XXX....  .
348UnknownUNKNOWNNon-etablieNON-ETABLIE UNK....  .
;;;;

%put ________Import Isocode dictionary Finished________;

proc sql;
create table &tmplib..PVLMM_Data5 as
select * from &tmplib..PVLMM_Data4
left outer join base_countries
on PVLMM_Data4.Reporting_country=base_countries.iso3code;
quit;

/*  Create Nationality_pers based upon Nat_orig - check for errors  */
/*  Path to Excel with list of corrected nationality iso codes  */

%global path;
%let path=\\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\REPORTING\TOOLS\PVLMM;
%global in_file;
%let in_file=RANCompileMacro_afterJuly2013.xlsx;

proc import out=&tmplib..nat_dictionary(rename=(Coor=nat_orig UNK=nat_dest)) replace
            datafile="&path.\&in_file"
            dbms=excelcs;
        range="Corr_code$A2:B800";
        usedate=yes;
        scantime=yes;
run;

libname shredstg '\\aofrontex.local\sas-shareddrive\SASData';

proc sql noprint;
    create table &tmplib..PVLMM_Data6 as
    select * from &tmplib..PVLMM_Data5 as t1
    left outer join &tmplib..nat_dictionary as t2
    on t1.nat_orig=t2.nat_orig;

    create table &tmplib..nat_labels as
    select distinct nationality_pers, nat_pers_label as temp
    from shredstg.fran_analysis;
quit;

data &tmplib..PVLMM_Data7;
set &tmplib..PVLMM_Data6;
    reporting_country_label=name;
    nationality_pers=nat_dest;
    if eu=1 or schengen=1 then do;
        'EU / WB'n='MS';
    end;
    else do;
        'EU / WB'n='WB';
        if Indicator='Return MS' then Indicator='Return WB';
    end;
    drop id name capname frename capfrename iso2code iso3code eu regionid subregionid visa regionname subregionname schengen nat_dest;
run;

proc sql noprint;
    create table &tmplib..PVLMM_Data_Final as
    select * from &tmplib..PVLMM_Data7 as t1
    left outer join &tmplib..nat_labels as t2
    on t1.nationality_pers=t2.nationality_pers;

    create table &tmplib..PVLMM_Data_Final2 as
    select * from &tmplib..PVLMM_Data_Final
    left outer join base_countries
    on PVLMM_Data_Final.returning_country_label=base_countries.name;
quit;

data &tmplib..PVLMM_Data_Final3;
set &tmplib..PVLMM_Data_Final2;
    format Week_Starting date9.;
    returning_country=iso3code;
    nat_pers_label=temp;

    drop temp drop id name capname frename capfrename iso2code iso3code eu regionid subregionid visa regionname subregionname schengen;
    Week=week(Week_Starting,'v');
    if week(Week_Starting,'v')<10 then do;
        YYYYWK=strip(strip(year(Week_Starting))||'Wk '||strip(week(Week_Starting,'v')));
    end;
    else do;
        YYYYWK=strip(strip(year(Week_Starting))||'Wk'||strip(week(Week_Starting,'v')));
    end;
    if month(Week_Starting)<10 then do;
        YYYYMM=strip(strip(year(Week_Starting))||'M '||strip(month(Week_Starting)));
    end;
    else do;
        YYYYMM=strip(strip(year(Week_Starting))||'M'||strip(month(Week_Starting)));
    end;
    Month=month(Week_Starting);
    Year=year(Week_Starting);

    if month(Week_Starting)<4 then YYYYQ=cats(Year,'Q1');
    if month(Week_Starting)>=4 and Month<7 then YYYYQ=cats(Year,'Q2');
    if month(Week_Starting)>=7 and Month<10 then YYYYQ=cats(Year,'Q3');
    if month(Week_Starting)>=10 then YYYYQ=cats(Year,'Q4');

    where Total ne 0 and Total ne . or Lost>0 or Stolen>0;

    if month=12 and week=1 then YYYYWK=strip(strip(year(Week_Starting)+1)||'Wk'||strip(week(Week_Starting,'v')));

    /*Adjusting weeks for 2010*/
    if datepart(Week_Starting)="01jan2010"d then YYYYWK="2009Wk53";
    if datepart(Week_Starting)="02jan2010"d then YYYYWK="2009Wk53";
    if datepart(Week_Starting)="03jan2010"d then YYYYWK="2009Wk53";

    /*Adjusting weeks for 2011*/
    if datepart(Week_Starting)="01jan2011"d then YYYYWK="2010Wk52";

    /*Adjusting weeks for 2012*/
    if datepart(Week_Starting)="31dec2012"d then YYYYWK="2013Wk 1";
    if datepart(Week_Starting)="01jan2012"d then YYYYWK="2011Wk52";

    /*Adjusting weeks for 2013*/
    if datepart(Week_Starting)="30dec2013"d then YYYYWK="2014Wk 1";
    if datepart(Week_Starting)="31dec2013"d then YYYYWK="2014Wk 1";

    if Failed_Asy="Y" then Failed_Asy="Yes";
    if Failed_Asy="N" then Failed_Asy="No";
run;

/*  Update whole dataset    */

data shredstg.PVLMM;
set shredstg.PVLMM;
where file ne "&file_upd";
*drop Forced Voluntary;
run;

data shredstg.PVLMM;
    if 0 then modify shredstg.PVLMM;
    set &tmplib..PVLMM_Data_Final3 open=defer;
    output;
run;