/*  Temporary JORA Tables */

/*Create library
LIBNAME shredstg BASE "\\Aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\SAS\sasdata\SAS_DB" ;
LIBNAME JORAOUT ACCESS "\\Aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\SAS\SASdata\Accessdata\JoraData.accdb" ;
*/

/*%macro Jorali%put nk;
%IF "&SysuserId." EQ "albertinellia"
%then LIBNAME JORARAUW ACCESS "\\Aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\SAS\SASdata\Accessdata\JoraLinkTony.accdb" ;
%IF "&SysuserId." EQ "bulea"
%then LIBNAME JORARAUW ACCESS "\\Aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\SAS\SASdata\Accessdata\JoraLinkAnca.accdb" ;
%IF "&SysuserId." EQ "doweyd"
%then LIBNAME JORARAUW ACCESS "\\Aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\SAS\SASdata\Accessdata\JORARAUlinkAll.accdb" ;
%mend;

%Joralink;*/


;
proc format;
    value fmt_joraIncStatus
    1 = "Pending"
    2 = "For LCC Verification"
    3 = "For ICC Verification"
    4 = "For FSC Verification"
    5 = "Accepted"
    other = "Unknown"
    ;
run;
data work.fmt_country_short;
    set jorarau.Country(keep=id ShortName);
    rename ID=Start ShortName=Label;
    fmtname='fmt_country_short';
run;
proc format cntlin=work.fmt_country_short;
run;
data work.fmt_country_long;
    set jorarau.Country(keep=id LongName);
    rename ID=Start LongName=Label;
    fmtname='fmt_country_long';
run;
proc format cntlin=work.fmt_country_long;
run;
data work.fmt_joraDict;
    set jorarau.Dictionary(keep=id Name);
    rename ID=Start Name=Label;
    fmtname='fmt_joraDict';
run;
proc format cntlin=work.fmt_joraDict;
run;
proc sql;
    create table test_ctr as
    select d.id as start, c.ShortName, c.LongName
    from jorarau.dictionary as d, jorarau.country as c
    where d.CountryId = c.Id
    ;
quit;
proc sql;
    create table test_bs as
    select d.id as Start, c.ShortName, c.LongName
    from jorarau.dictionary as d, jorarau.country as c
    where d.BorderSectionId = c.Id
    ;
quit;
data test_ctr_short;
    set test_ctr(keep=start ShortName) end=koniec;
    rename ShortName=Label;
    fmtname="fmt_bs_p1_short";
    if koniec then do;
        HLO="O";
        Start = .;
        ShortName="";
        output;
    end;
run;
data test_ctr_long;
    set test_ctr(keep=start LongName) end=koniec;
    rename LongName=Label;
    fmtname="fmt_bs_p1_long";
    if koniec then do;
        HLO="O";
        Start = .;
        LongName="";
        output;
    end;
run;
proc format cntlin=test_ctr_short;
run;
proc format cntlin=test_ctr_long;
run;
data test_bs_short;
    set test_bs(keep=start ShortName) end=koniec;
    rename ShortName=Label;
    fmtname="fmt_bs_p2_short";
    if koniec then do;
        HLO="O";
        Start = .;
        ShortName="";
        output;
    end;
run;
data test_bs_long;
    set test_bs(keep=start LongName) end=koniec;
    length HLO $1.;
    rename LongName=Label;
    fmtname="fmt_bs_p2_long";
    output;
    if koniec then do;
        HLO="O";
        Start = .;
        LongName="";
        output;
    end;
run;
proc format cntlin=test_bs_short;
run;
proc format cntlin=test_bs_long;
run;



%macro FormatValue(src, fmt);
    case when missing(&src.) eq 0 then strip(put(&src., &fmt..)) else "" end
%mend;
%macro DelTbl(lib, prf);
    proc datasets lib=&lib. nolist nowarn;
        delete &prf.:;
    quit;
%mend;





/* Drop tables from Access database */
options fmtsearch=(work);
options compress=yes;

     /*JORA_nat format*/
     DATA WORK._EG_CFMT;
         LENGTH label $ 44;
         SET JORARAU.Country (KEEP=ID longname RENAME=(ID=start longname=label));
         RETAIN fmtname "Jora_Nat" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;

     PROC SQL;
         DROP TABLE WORK._EG_CFMT, work.location;
     QUIT;

     /*Format from label to isocode*/
      DATA WORK._EG_CFMT;
         LENGTH label $ 44;
         SET JORARAU.Country (KEEP=longname shortname RENAME=(longname=start shortname=label));
         RETAIN fmtname "LabelToIso" type "C";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;

     PROC SQL;
         DROP TABLE WORK._EG_CFMT, work.location;
     QUIT;

DATA WORK._EG_CFMT;
         LENGTH label $ 40;
         SET JORARAU.DICTIONARY (KEEP=Id name RENAME=(Id=start name=label));
         RETAIN fmtname "DICTIONARY" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;


     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;

     /*JORA_natcode format*/
     DATA WORK._EG_CFMT;
         LENGTH label $ 44;
         SET JORARAU.Country (KEEP=ID shortname RENAME=(ID=start shortname=label));
         RETAIN fmtname "Jora_isocode" /*type "T"*/;
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;

     PROC SQL;
         DROP TABLE WORK._EG_CFMT, work.location;
     QUIT;


     DATA WORK._EG_CFMT;
         LENGTH label $ 32;
         SET JORARAU.systemUser (KEEP=Id Email RENAME=(Id=start Email=label));
         RETAIN fmtname "SYS_USER" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;


     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;

     DATA WORK._EG_CFMT;
         LENGTH label $ 32;
         SET JORARAU.LOCATION (KEEP=Id Name RENAME=(Id=start Name=label));
         RETAIN fmtname "JORA_OP_AREA" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;

     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;

     /*Preparation of tmp dataset location in order to preapre further formats*/

     data location(keep=Id name parentname bordersection memberstate keybordersection latitude longitude reportinglocationid);
     set JORARAU.location;
     length parentname $30 bordersection $30 memberstate $30 keybordersection $50;
     ParentName=put(parentlocationid,JORA_OP_AREA.);
     bordersection=put(bordersectionid,Jora_Nat.);
     memberstate=put(memberstateid,Jora_Nat.);
     if (trim(bordersection) NE " ")
     then Keybordersection=trim(memberstate)||"-"||trim(bordersection);
     run;



     /*Parent_loc format*/
     DATA WORK._EG_CFMT;
         LENGTH label $ 40;
         SET WORK.LOCATION (KEEP=Id parentname RENAME=(Id=start parentname=label));
         RETAIN fmtname "JORA_LOC_PARENT" type "N";

         end=start;
     RUN;


     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;

     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;


     /* JORA_LOC_BS format */
     DATA WORK._EG_CFMT;
         LENGTH label $ 40;
         SET WORK.LOCATION (KEEP=Id bordersection RENAME=(Id=start bordersection=label));
         RETAIN fmtname "JORA_LOC_BS" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;


     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;


     /*JORA_LOC_MS format*/
     DATA WORK._EG_CFMT;
         LENGTH label $ 40;
         SET WORK.LOCATION (KEEP=Id memberstate RENAME=(Id=start memberstate=label));
         RETAIN fmtname "JORA_LOC_MS" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;


     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;

     /*JORA_LOC_KBS format*/
     DATA WORK._EG_CFMT;
         LENGTH label $ 50;
         SET WORK.LOCATION (KEEP=Id keybordersection RENAME=(Id=start keybordersection=label));
         RETAIN fmtname "JORA_LOC_KBS" type "N";
         end=start;
     RUN;

     PROC FORMAT library=work CNTLIN=WORK._EG_CFMT;
     RUN;

     PROC SQL;
         DROP TABLE WORK._EG_CFMT;
     QUIT;



     /**************************/
     /*End of format definition*/
     /*************************/
     PROC SQL;*/
        CREATE TABLE work.JORA_NAT_PrimaryOperation AS
        SELECT t4.Name as OperationName,
              put(t4.operationalScopeid,DICTIONARY.) as OperationalScope,
              "Primary" as OperationReporting,
              put(t4.operationsectortypeid,DICTIONARY.) as SectorType,
              put(t1.OperationalAreaId,JORA_OP_AREA.) as OperationalArea,
              put(t1.OperationalAreaId,JORA_LOC_PARENT.) as ParentLocation,
              put(t1.OperationalAreaId,JORA_LOC_BS.) as Bordersection,
              put(t1.OperationalAreaId,JORA_LOC_MS.) as MemberState,
              put(t1.OperationalAreaId,JORA_LOC_KBS.) as KeyBorderSection,
              t1.statusid, /*Status of the incident
              put(t1.approverID,Sys_user.) as ApproverID,
              t1.DetectedBy,
              t1.InterceptionDate,
              put(datepart(t1.InterceptionDate),YYMMDD10.) as InterceptionDate_day,
              put(timepart(t1.InterceptionDate),time8.) as InterceptionDate_time,
              put(datepart(t1.InterceptionDate),YYMM.) as Interceptiondate_YYYYMM,
              t1.detectiontime,
              put(datepart(t1.detectiontime),YYMMDD10.) as detectiontime_day,
              put(timepart(t1.detectiontime),time8.) as detectiontime_time,
              put(datepart(t1.detectiontime),YYMM.) as detectiontime_YYYYMM,
              t1.InterceptionPlaceComments,
              t1.InterceptedBy,
              t1.CreateDate,
              put(datepart(t1.CreateDate),YYMMDD10.) as CreateDate_day,
              put(timepart(t1.CreateDate),time8.) as CreateDate_time,
              put(datepart(t1.CreateDate),YYMM.) as Createdate_YYYYMM,
              put(year(datepart(t1.CreateDate)),4.)||"Wk"||put(week(datepart(t1.CreateDate),'v'),2.) as Createdate_YYYYWK,
              t1.Latitude,
              t1.LatitudeDetection,
              t1.Longitude,
              t1.LongitudeDetection,
              t1.TimeOfDeparture,
              t1.Disembarkation,
              t1.PlaceOfDeparture,
              t1.Comments,
              t1.DeathCases,
              t1.IncidentNumber,
              t1.MarketValue,
              t1.MigrantsDeterred,
              t1.TransportMeansNumber,
              t1.SearchAndRescueInvolved,
              t1.SmugglingOfNumber,
              t1.make,
              t1.model,
              put(t1.platenationalityid,Jora_nat.) as PlateNationality,
              put(t1.drivernationalityid,Jora_nat.) as DriverNationality,
              put(t1.countryofdepartureID,Jora_nat.) as CountryOfDeparture,
              put(t1.countryofdestinationID,Jora_nat.) as CountryOfDestination,
              put(t1.ReferenceId,DICTIONARY.) as RefOperationalArea,
              put(t1.IncidentTypeId,DICTIONARY.) as IncidentType,
              put(t1.BoatDestroyedById,DICTIONARY.) as BoatDestroyedBy,
              put(t1.SmugglingOfId,DICTIONARY.) as SmugglingOf,
              put(t1.PlaceOfInterceptionId ,DICTIONARY.) as PlaceOfInterception,
              put(t1.ZoneId ,DICTIONARY.) As Zone,
              put(t1.DestinationMethodOfTransportId ,DICTIONARY.) as TransportType,
              t3.nationalityclaimedID,
              t3.nationalitypresumedID,
              t3.nationalityConfirmedID,
              put(t3.RefusalReasonId ,DICTIONARY.) as Nat_RefusalReason,
              put(t3.PersonRoleId ,DICTIONARY.) as Nat_PersonRole,
              t3.NumberOfPersons,
              t3.NumberOfAdultsMale,
              t3.NumberOfMinorFemale,
              t3.NumberOfAdultsFemale,
              t3.NumberOfMinorMale,
              count(incidentnumber) as NbRecord
          FROM JORARAU.Incident t1
          INNER JOIN JORARAU.NationalityInformation t3 ON (t1.Id = t3.incidentId)
          INNER JOIN JORARAU.Operation t4 ON (t1.OperationId = t4.Id)
          Where t1.isdeleted NE 1 and t1.isaccepted=1
          group by OperationName, IncidentNumber
        ORDER BY t1.InterceptionDate DESC;/*

CREATE TABLE work.JORA_NAT_SecondaryOperation AS
        SELECT t4.Name as OperationName,
              put(t4.operationalScopeid,DICTIONARY.) as OperationalScope,
              "Secondary" as OperationReporting,
              put(t4.operationsectortypeid,DICTIONARY.) as SectorType,
              put(t1.OperationalAreaId,JORA_OP_AREA.) as OperationalArea,
              put(t1.OperationalAreaId,JORA_LOC_PARENT.) as ParentLocation,
              put(t1.OperationalAreaId,JORA_LOC_BS.) as Bordersection,
              put(t1.OperationalAreaId,JORA_LOC_MS.) as MemberState,
              put(t1.OperationalAreaId,JORA_LOC_KBS.) as KeyBorderSection,
              t1.statusid, Status of the incident
              put(t1.approverID,Sys_user.) as ApproverID,
              t1.DetectedBy,
              t1.InterceptionDate,
              put(datepart(t1.InterceptionDate),YYMMDD10.) as InterceptionDate_day,
              put(timepart(t1.InterceptionDate),time8.) as InterceptionDate_time,
              put(datepart(t1.InterceptionDate),YYMM.) as Interceptiondate_YYYYMM,
              t1.detectiontime,
              put(datepart(t1.detectiontime),YYMMDD10.) as detectiontime_day,
              put(timepart(t1.detectiontime),time8.) as detectiontime_time,
              put(datepart(t1.detectiontime),YYMM.) as detectiontime_YYYYMM,
              t1.InterceptionPlaceComments,
              t1.InterceptedBy,
              t1.CreateDate,
              put(datepart(t1.CreateDate),YYMMDD10.) as CreateDate_day,
              put(timepart(t1.CreateDate),time8.) as CreateDate_time,
              put(datepart(t1.CreateDate),YYMM.) as Createdate_YYYYMM,
              put(year(datepart(t1.CreateDate)),4.)||"Wk"||put(week(datepart(t1.CreateDate),'v'),2.) as Createdate_YYYYWK,
              t1.Latitude,
              t1.LatitudeDetection,
              t1.Longitude,
              t1.LongitudeDetection,
              t1.TimeOfDeparture,
              t1.Disembarkation,
              t1.PlaceOfDeparture,
              t1.Comments,
              t1.DeathCases,
              t1.IncidentNumber,
              t1.MarketValue,
              t1.MigrantsDeterred,
              t1.TransportMeansNumber,
              t1.SearchAndRescueInvolved,
              t1.SmugglingOfNumber,
              t1.make,
              t1.model,
              put(t1.platenationalityid,Jora_nat.) as PlateNationality,
              put(t1.drivernationalityid,Jora_nat.) as DriverNationality,
              put(t1.countryofdepartureID,Jora_nat.) as CountryOfDeparture,
              put(t1.countryofdestinationID,Jora_nat.) as CountryOfDestination,
              put(t1.ReferenceId,DICTIONARY.) as RefOperationalArea,
              put(t1.IncidentTypeId,DICTIONARY.) as IncidentType,
              put(t1.BoatDestroyedById,DICTIONARY.) as BoatDestroyedBy,
              put(t1.SmugglingOfId,DICTIONARY.) as SmugglingOf,
              put(t1.PlaceOfInterceptionId ,DICTIONARY.) as PlaceOfInterception,
              put(t1.ZoneId ,DICTIONARY.) As Zone,
              put(t1.DestinationMethodOfTransportId ,DICTIONARY.) as TransportType,
              t3.nationalityclaimedID,
              t3.nationalitypresumedID,
              t3.nationalityConfirmedID,
              put(t3.RefusalReasonId ,DICTIONARY.) as Nat_RefusalReason,
              put(t3.PersonRoleId ,DICTIONARY.) as Nat_PersonRole,
              t3.NumberOfPersons,
              t3.NumberOfAdultsMale,
              t3.NumberOfMinorFemale,
              t3.NumberOfAdultsFemale,
              t3.NumberOfMinorMale,
              count(incidentnumber) as NbRecord
          FROM JORARAU.Incident t1
          INNER JOIN JORARAU.NationalityInformation t3 ON (t1.Id = t3.incidentId)
          INNER JOIN JORARAU.IncidentRelatedOperation t2 ON (t1.Id = t2.IncidentId)
          INNER JOIN JORARAU.Operation t4 ON (t2.OperationId = t4.Id)
          Where t1.isdeleted NE 1  and t1.isaccepted=1
          group by OperationName, IncidentNumber
        ORDER BY t1.InterceptionDate DESC*/


     CREATE TABLE work.JORA_MIG_PrimaryOperation AS
        SELECT t4.Name as OperationName,
              put(t4.operationalScopeid,DICTIONARY.) as OperationalScope,
              "Primary" as OperationReporting,
              put(t4.operationsectortypeid,DICTIONARY.) as SectorType,
/*            put(t1.OperationalAreaId,JORA_OP_AREA.) as OperationalArea,*/
              put(t1.OperationalAreaId,JORA_LOC_PARENT.) as ParentLocation,
/*            put(t1.OperationalAreaId,JORA_LOC_BS.) as Bordersection,*/
/*            put(t1.OperationalAreaId,JORA_LOC_MS.) as MemberState,*/
/*            put(t1.OperationalAreaId,JORA_LOC_KBS.) as KeyBorderSection,*/
              t1.DetectedBy,
              t1.InterceptionDate,
              put(datepart(t1.InterceptionDate),YYMMDD10.) as InterceptionDate_day,
              put(timepart(t1.InterceptionDate),time8.) as InterceptionDate_time,
              put(datepart(t1.InterceptionDate),YYMM.) as Interceptiondate_YYYYMM,
              t1.detectiontime,
              put(datepart(t1.detectiontime),YYMMDD10.) as detectiontime_day,
              put(timepart(t1.detectiontime),time8.) as detectiontime_time,
              put(datepart(t1.detectiontime),YYMM.) as detectiontime_YYYYMM,
              t1.InterceptionPlaceComments,
              t1.InterceptedBy,
              t1.CreateDate,
              t1.statusid, /*Status of the incident*/
              put(t1.approverID,Sys_user.) as ApproverID,
              put(datepart(t1.CreateDate),YYMMDD10.) as CreateDate_day,
              put(timepart(t1.CreateDate),time8.) as CreateDate_time,
              put(datepart(t1.CreateDate),YYMM.) as Createdate_YYYYMM,
              put(year(datepart(t1.CreateDate)),4.)||"Wk"||put(week(datepart(t1.CreateDate),'v'),2.) as Createdate_YYYYWK,
              t1.make,
              t1.model,
              put(t1.platenationalityid,Jora_nat.) as PlateNationality,
              put(t1.drivernationalityid,Jora_nat.) as DriverNationality,
              t1.Latitude,
              t1.LatitudeDetection,
              t1.Longitude,
              t1.LongitudeDetection,
              t1.TimeOfDeparture,
              t1.Disembarkation,
              t1.PlaceOfDeparture,
              t1.Comments,
              t1.DeathCases,
              t1.IncidentNumber,
              t1.MarketValue,
              t1.MigrantsDeterred,
              t1.TransportMeansNumber,
              t1.SearchAndRescueInvolved,
              t1.SmugglingOfNumber,
              put(t1.countryofdepartureID,Jora_nat.) as CountryOfDeparture,
              put(t1.countryofdepartureID,Jora_isocode.) as CountryOfDeparture_iso,
              put(t1.countryofdestinationID,Jora_nat.) as CountryOfDestination,
              put(t1.countryofdestinationID,Jora_isocode.) as CountryOfDestination_iso,
              put(t1.ReferenceId,DICTIONARY.) as RefOperationalArea,
               put(t1.IncidentTypeId,DICTIONARY.) as IncidentType,
               put(t1.BoatDestroyedById,DICTIONARY.) as BoatDestroyedBy,
               put(t1.SmugglingOfId,DICTIONARY.) as SmugglingOf,
               put(t1.PlaceOfInterceptionId ,DICTIONARY.) as PlaceOfInterception,
/*               put(t1.ZoneId ,DICTIONARY.) As Zone,*/
               put(t1.DestinationMethodOfTransportId ,DICTIONARY.) as TransportType,
              t2.firstname,
              t2.lastname,
              put(t2.outcomeid,DICTIONARY.) as outcome,
              t2.nationalityID,
              t2.nationalityclaimedID,
              t2.nationalitypresumedID,
              put(t2.GenderId ,DICTIONARY.) as Gender,
              t2.age,
               put(t2.ImmigrationStatusId ,DICTIONARY.) as ImmigrationStatusId,
               put(t2.PersonRoleId ,DICTIONARY.) as Mig_PersonRole,
               put(t2.RefusalReasonId ,DICTIONARY.) as Mig_RefusalReason,
               1 as frequency,
              count(incidentnumber) as NbRecord,
              t1.GuestOfficerInvolved as GuestOfficerInvolved
           FROM JORARAU.Incident t1
          INNER JOIN JORARAU.MigrantInformation t2 ON (t1.Id = t2.IncidentId)
          INNER JOIN JORARAU.Operation t4 ON (t1.OperationId = t4.Id)
         Where t1.isdeleted NE 1  and t1.isaccepted=1
         group by OperationName, IncidentNumber
        ORDER BY t1.InterceptionDate DESC;

 CREATE TABLE work.JORA_MIG_SecondaryOperation AS
        SELECT t4.Name as OperationName,
              put(t4.operationalScopeid,DICTIONARY.) as OperationalScope,
              "Secondary" as OperationReporting,
              put(t4.operationsectortypeid,DICTIONARY.) as SectorType,
/*            put(t1.OperationalAreaId,JORA_OP_AREA.) as OperationalArea,*/
              put(t1.OperationalAreaId,JORA_LOC_PARENT.) as ParentLocation,
/*            put(t1.OperationalAreaId,JORA_LOC_BS.) as Bordersection,*/
/*            put(t1.OperationalAreaId,JORA_LOC_MS.) as MemberState,*/
/*            put(t1.OperationalAreaId,JORA_LOC_KBS.) as KeyBorderSection,*/
              t1.DetectedBy,
              t1.InterceptionDate,
              put(datepart(t1.InterceptionDate),YYMMDD10.) as InterceptionDate_day,
              put(timepart(t1.InterceptionDate),time8.) as InterceptionDate_time,
              put(datepart(t1.InterceptionDate),YYMM.) as Interceptiondate_YYYYMM,
              t1.detectiontime,
              put(datepart(t1.detectiontime),YYMMDD10.) as detectiontime_day,
              put(timepart(t1.detectiontime),time8.) as detectiontime_time,
              put(datepart(t1.detectiontime),YYMM.) as detectiontime_YYYYMM,
              t1.InterceptionPlaceComments,
              t1.InterceptedBy,
              t1.CreateDate,
              t1.statusid, /*Status of the incident*/
              put(t1.approverID,Sys_user.) as ApproverID,
              put(datepart(t1.CreateDate),YYMMDD10.) as CreateDate_day,
              put(timepart(t1.CreateDate),time8.) as CreateDate_time,
              put(datepart(t1.CreateDate),YYMM.) as Createdate_YYYYMM,
              put(year(datepart(t1.CreateDate)),4.)||"Wk"||put(week(datepart(t1.CreateDate),'v'),2.) as Createdate_YYYYWK,
              t1.make,
              t1.model,
              put(t1.platenationalityid,Jora_nat.) as PlateNationality,
              put(t1.drivernationalityid,Jora_nat.) as DriverNationality,
              t1.Latitude,
              t1.LatitudeDetection,
              t1.Longitude,
              t1.LongitudeDetection,
              t1.TimeOfDeparture,
              t1.Disembarkation,
              t1.PlaceOfDeparture,
              t1.Comments,
              t1.DeathCases,
              t1.IncidentNumber,
              t1.MarketValue,
              t1.MigrantsDeterred,
              t1.TransportMeansNumber,
              t1.SearchAndRescueInvolved,
              t1.SmugglingOfNumber,
              put(t1.countryofdepartureID,Jora_nat.) as CountryOfDeparture,
              put(t1.countryofdepartureID,Jora_isocode.) as CountryOfDeparture_iso,
              put(t1.countryofdestinationID,Jora_nat.) as CountryOfDestination,
              put(t1.countryofdestinationID,Jora_isocode.) as CountryOfDestination_iso,
              put(t1.ReferenceId,DICTIONARY.) as RefOperationalArea,
              put(t1.IncidentTypeId,DICTIONARY.) as IncidentType,
              put(t1.BoatDestroyedById,DICTIONARY.) as BoatDestroyedBy,
              put(t1.SmugglingOfId,DICTIONARY.) as SmugglingOf,
              put(t1.PlaceOfInterceptionId ,DICTIONARY.) as PlaceOfInterception,
/*              put(t1.ZoneId ,DICTIONARY.) As Zone,*/
              put(t1.DestinationMethodOfTransportId ,DICTIONARY.) as TransportType,
              t2.firstname,
              t2.lastname,
              put(t2.outcomeid,DICTIONARY.) as outcome,
              t2.nationalityID,
              t2.nationalityclaimedID,
              t2.nationalitypresumedID,
              put(t2.GenderId ,DICTIONARY.) as Gender,
              t2.age,
              put(t2.ImmigrationStatusId ,DICTIONARY.) as ImmigrationStatusId,
              put(t2.PersonRoleId ,DICTIONARY.) as Mig_PersonRole,
              put(t2.RefusalReasonId ,DICTIONARY.) as Mig_RefusalReason,
               1 as frequency,
              count(incidentnumber) as NbRecord,
              t1.GuestOfficerInvolved as GuestOfficerInvolved
           FROM JORARAU.Incident t1
          INNER JOIN JORARAU.MigrantInformation t2 ON (t1.Id = t2.incidentId)
          INNER JOIN JORARAU.IncidentRelatedOperation t3 ON (t1.Id = t3.IncidentId)
          INNER JOIN JORARAU.Operation t4 ON (t3.OperationId = t4.Id)
         Where t1.isdeleted NE 1  and t1.isaccepted=1
         group by OperationName, IncidentNumber
        ORDER BY t1.InterceptionDate DESC;

quit;
 /*data shredstg.JORA_NAT;
set work.Jora_nat_PrimaryOperation work.Jora_nat_SecondaryOperation;
run;*/

*LOCK SHREDSTG.JORA_MIG;

data work.Jora_Mig_temp;
set work.Jora_Mig_PrimaryOperation work.Jora_Mig_SecondaryOperation;
run;

/*
     data shredstg.JORA_NAT;
     length Status $30 Nat_final_iso $3 Nat_final $50 Nat_confirmed $50 Nat_presumed $50 Nat_claimed $50 route $50 LastDepartureCtry $50;

     set shredstg.JORA_NAT;
     if statusid=1 then status='Pending';
     if statusid=2 then status='For LCC Verification';
     if statusid=3 then status='For ICC Verification';
     if statusid=4 then status='For FSC Verification';
     if statusid=5 then status='Accepted';

     NatClaimedAndConfirmed = strip(Nat_nationality_confirmed);
     if(NatClaimedAndConfirmed eq '.') then NatClaimedAndConfirmed = strip(Nat_nationality_claimed);

     if sectortype in ("AIR","LAND") and Detectiontime=. then
        do;

            IncidentDate=CreateDate;
            IncidentDate_Day= put(datepart(CreateDate),YYMMDD10.);
            IncidentDate_Time= put(datepart(CreateDate),time8.);
            IncidentDate_YYYYMM= put(datepart(CreateDate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(CreateDate)),4.)||"Wk"||put(week(datepart(CreateDate),'v'),2.);

        end;
    else if sectortype in ("AIR","LAND") and Detectiontime <>. then
        do;
            IncidentDate=detectiontime;
            IncidentDate_Day= put(datepart(detectiontime),YYMMDD10.);
            IncidentDate_Time= put(datepart(detectiontime),time8.);
            IncidentDate_YYYYMM= put(datepart(detectiontime),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(detectiontime)),4.)||"Wk"||put(week(datepart(detectiontime),'v'),2.) ;
        end;
    else if sectortype = "SEA" and interceptiondate=. then
        do;
            IncidentDate=CreateDate;
            IncidentDate_Day= put(datepart(CreateDate),YYMMDD10.);
            IncidentDate_Time= put(datepart(CreateDate),time8.);
            IncidentDate_YYYYMM= put(datepart(CreateDate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(CreateDate)),4.)||"Wk"||put(week(datepart(CreateDate),'v'),2.);
        end;
    else if sectortype = "SEA" and interceptiondate NE . then
        do;
            IncidentDate=interceptiondate;
            IncidentDate_Day= put(datepart(interceptiondate),YYMMDD10.);
            IncidentDate_Time= put(datepart(interceptiondate),time8.);
            IncidentDate_YYYYMM= put(datepart(interceptiondate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(interceptiondate)),4.)||"Wk"||put(week(datepart(interceptiondate),'v'),2.);
        end;


     SmugglingNbAdjusted=SmugglingOfNumber/nbrecord;
     drop statusid;


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

 /*"Final" nationality calculation

    Nat_confirmed=put(nationalityConfirmedID,Jora_nat.);
    Nat_claimed=put(nationalityclaimedID,Jora_nat.);
    Nat_presumed=put(nationalitypresumedID,Jora_nat.);


        if strip(nat_confirmed) Not in (".",""," ") then do;
                                                Nat_Final=Nat_confirmed;
                                                Nat_final_iso= put(nationalityconfirmedID,Jora_isocode.);
                                                end;
        else if strip(Nat_presumed) Not in (".",""," ") then do;
                                                            Nat_Final=Nat_presumed;
                                                            Nat_final_iso= put(nationalitypresumedID,Jora_isocode.);
                                                            end;

                else if strip(Nat_claimed) Not in (".",""," ") then do;
                                                            Nat_Final=Nat_claimed;
                                                            Nat_final_iso= put(nationalityclaimedID,Jora_isocode.);
                                                            end;
            /*end nationnality computation

IF OperationName='EPN-INDALO 2012' and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing', 'Other', 'Smuggling', 'Migrants deterred') and Mig_PersonRole in ('.', 'Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route='Western Mediterranean Route (ESP-land and sea)';
IF OperationName='EPN HERA 2012' and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing', 'Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route=' Western African Route';
IF OperationName in ('EPN-AENEAS 2011','EPN Aeneas 2012') and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route='Central Mediterranean Route (ITA-Apulia&Calabria)';
IF OperationName in ('EPN-HERMES 2011','EPN HERMES 2012') and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing', 'Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route='Central Mediterranean Route (ITA and MLT, excludin';
IF (OperationName in ('Poseidon Land 2013','Poseidon Land 2012','Poseidon Land 2011 Extension') and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing', 'hiding in transportation means','Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor')) Or (OperationName in ('Poseidon Sea 2013','Poseidon Sea 2012','Poseidon Sea 2011') and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing', 'hiding in transportation means','Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated','Other','Unaccompanied minor') and Zone in ('Op. Area A','Op. Area K')) then Route= 'Eastern Mediterranean route (GRC,BGR,CYP-land and';
IF OperationName in ('Poseidon Sea 2013','Poseidon Sea 2012','Poseidon Sea 2011') and Status='Accepted' and IncidentType in ('Facilitation - smuggling of humans', 'Illegal border crossing', 'hiding in transportation means','Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') and Zone='Op. Area B' then Route= 'Secondary movements to ITA';
if Sectortype='LAND'  then LastDepartureCtry=Bordersection;
if Sectortype='SEA'  then LastDepartureCtry=CountryofDeparture;
if strip(LastDepartureCtry) in ('.','','') then LastDepartureCtry='Unknown';
format IncidentDate datetime.;
run;
*/

 data work.jora_mig_temp;
    length OperationNameGroup $40Status $30 route $50 LastdepartureCtry $50 Nat_final_iso $3 Nat_final $50 Nat_confirmed $50 Nat_presumed $50 Nat_claimed $50 GuestOfficerInvolved1 $5;
    set work.jora_mig_temp;

/*Statusid enumeration*/

    if statusid=1 then
        status='Pending';
    if statusid=2 then
        status='For LCC Verification';
    if statusid=3 then
        status='For ICC Verification';
    if statusid=4 then
        status='For FSC Verification';
    if statusid=5 then
        status='Accepted';
    drop statusid;

    if sectortype in ("AIR","LAND") and Detectiontime=. then
        do;
            IncidentDate=CreateDate;
            IncidentDate_Day= put(datepart(CreateDate),YYMMDD10.);
            IncidentDate_Time= put(datepart(CreateDate),time8.);
            IncidentDate_YYYYMM= put(datepart(CreateDate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(CreateDate)),4.)||"Wk"||put(week(datepart(CreateDate),'v'),2.) ;
        end;
    else if sectortype in ("AIR","LAND") and Detectiontime <>. then
        do;
            IncidentDate=detectiontime;
            IncidentDate_Day= put(datepart(detectiontime),YYMMDD10.);
            IncidentDate_Time= put(datepart(detectiontime),time8.);
            IncidentDate_YYYYMM= put(datepart(detectiontime),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(detectiontime)),4.)||"Wk"||put(week(datepart(detectiontime),'v'),2.) ;
        end;
    else if sectortype = "SEA" and interceptiondate=. then
        do;
            IncidentDate=CreateDate;
            IncidentDate_Day= put(datepart(CreateDate),YYMMDD10.);
            IncidentDate_Time= put(datepart(CreateDate),time8.);
            IncidentDate_YYYYMM= put(datepart(CreateDate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(CreateDate)),4.)||"Wk"||put(week(datepart(CreateDate),'v'),2.);
        end;
    else if sectortype = "SEA" and interceptiondate<>. then
        do;
            IncidentDate=interceptiondate;
            IncidentDate_Day= put(datepart(interceptiondate),YYMMDD10.);
            IncidentDate_Time= put(datepart(interceptiondate),time8.);
            IncidentDate_YYYYMM= put(datepart(interceptiondate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(interceptiondate)),4.)||"Wk"||put(week(datepart(interceptiondate),'v'),2.) ;
        end;
         SmugglingNbAdjusted=SmugglingOfNumber/nbrecord;


         /*"Final" nationality calculation*/
    Nat_confirmed=put(nationalityID,Jora_nat.);
    Nat_claimed=put(nationalityclaimedID,Jora_nat.);
    Nat_presumed=put(nationalitypresumedID,Jora_nat.);

    if strip(nat_confirmed) Not in (".",""," ") then do;
                                                Nat_Final=Nat_confirmed;
                                                Nat_final_iso= put(nationalityID,Jora_isocode.);
                                                end;
        else if strip(Nat_presumed) Not in (".",""," ") then do;
                                                            Nat_Final=Nat_presumed;
                                                            Nat_Final_iso= put(nationalitypresumedID,Jora_isocode.);
                                                            end;

                else if strip(Nat_claimed) Not in (".",""," ") then do;
                                                            Nat_Final=Nat_claimed;
                                                            Nat_Final_iso=put(nationalityclaimedID,Jora_isocode.);
                                                            end;
            /*end nationnality computation*/


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

if datepart(IncidentDate)="31dec2012"d then IncidentDate_YYYYWK="2013Wk 1";
if datepart(IncidentDate)="01jan2012"d then IncidentDate_YYYYWK="2011Wk52";

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

if datepart(IncidentDate)="30dec2013"d then IncidentDate_YYYYWK="2014Wk 1";
if datepart(IncidentDate)="31dec2013"d then IncidentDate_YYYYWK="2014Wk 1";


/*Routes filters(to be udpated)*/
IF OperationName in ('EPN-INDALO 2012','EPN-INDALO 2013') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing', 'Other', 'Smuggling of goods', 'Migrants deterred') and Mig_PersonRole in ('.', 'Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route='Western Mediterranean Route (ESP-land and sea)';
IF OperationName in('EPN HERA 2012','EPN HERA 2013') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing', 'Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route=' Western African Route';
IF OperationName in ('EPN-AENEAS 2011','EPN Aeneas 2012','EPN Aeneas 2013') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route='Central Mediterranean Route (ITA-Apulia&Calabria)';
IF OperationName in ('EPN-HERMES 2011','EPN HERMES 2012','EPN HERMES 2013') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing', 'Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') then Route='Central Mediterranean Route (ITA and MLT, excludin';
IF (OperationName in ('Poseidon Land 2013','Poseidon Land 2012','Poseidon Land 2011 Extension') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing', 'Hiding in transportation means / clandestine','Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor')) Or (OperationName in ('Poseidon Sea 2013','Poseidon Sea 2012','Poseidon Sea 2011') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing', 'Hiding in transportation means / clandestine','Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated','Other','Unaccompanied minor') and Zone in ('Op. Area A','Op. Area K')) then Route= 'Eastern Mediterranean route (GRC,BGR,CYP-land and';
IF OperationName in ('Poseidon Sea 2013','Poseidon Sea 2012','Poseidon Sea 2011') and Status='Accepted' and IncidentType in ('Facilitation (smuggling of humans)', 'Illegal border crossing', 'Hiding in transportation means / clandestine','Other') and Mig_PersonRole in ('Irregular migrant', 'Irregular migrant facilitated', 'Other', 'Unaccompanied minor') and Zone='Op. Area B' then Route= 'Secondary movements to ITA';
if Sectortype='LAND'  then LastDepartureCtry=Bordersection;
if Sectortype='SEA'  then LastDepartureCtry=CountryofDeparture;
if strip(LastDepartureCtry) in ('.','','') then LastDepartureCtry='Unknown';
format IncidentDate datetime.;

/*Group operation for year to year comparison*/
    operationnamegroup=operationname;
    if upcase(operationname) in ('EPN AENEAS 2012','EPN AENEAS 2013','EPN-AENEAS 2011') then operationNameGroup="Aeneas";
    if upcase(operationname) in ('EPN HERMES 2012','EPN HERMES 2013','EPN-HERMES 2011') then operationNameGroup="Hermes";
    if upcase(operationname) in ('EPN-INDALO 2012','EPN-INDALO 2013') then operationNameGroup="Indalo";
    if upcase(operationname) in ('FOCAL POINTS 2011 LAND  EXTENSION','FOCAL POINTS 2012 LAND','FOCAL POINTS 2013 LAND') then operationNameGroup="Focal Points Land";
    if upcase(operationname) in ('FOCAL POINTS SEA 2012','FOCAL POINTS SEA 2013') then operationNameGroup="Focal Points Sea";
    if upcase(operationname) in ('POSEIDON LAND 2011 EXTENSION','POSEIDON LAND 2012','POSEIDON LAND 2013') then operationNameGroup="Poseidon Land";
    if upcase(operationname) in ('POSEIDON SEA 2011','POSEIDON SEA 2011 REPORTING POINTS','POSEIDON SEA 2012','POSEIDON SEA 2013') then operationNameGroup="Poseidon Sea";

if GuestOfficerInvolved=0 then GuestOfficerInvolved1="No";
if GuestOfficerInvolved=1 then GuestOfficerInvolved1="Yes";
Drop GuestOfficerInvolved;

run;

/*export data to sql*/
*proc sql;
*drop table frandata.JORA_MIG;/*, frandata.JORA_NAT;*/
*quit;

/*data frandata.JORA_Mig;
set shredstg.JORA_mig;
run;*/

/*
data frandata.JORA_nat;
set shredstg.JORA_NAT;
run;*/

/*Dictionary updates in the last 30 days*/
proc sql;
create table dic_Update as
select *
from JORARAU.DICTIONARY
where datepart(updatedate)>="&SYSDATE9."d-30;

drop table /*location*/ JORA_NAT_PrimaryOperation, JORA_NAT_SecondaryOperation, JORA_MIG_PrimaryOperation, JORA_MIG_SecondaryOperation;
quit;


/*Document tables*/
proc sql;
CREATE TABLE work.JORA_PersonDoc AS
        SELECT t4.Name as OperationName,
              put(t4.operationalScopeid,DICTIONARY.) as OperationalScope,
              put(t4.operationsectortypeid,DICTIONARY.) as SectorType,
              /*Incident variables*/
/*            put(t1.OperationalAreaId,JORA_OP_AREA.) as OperationalArea,*/
              put(t1.OperationalAreaId,JORA_LOC_PARENT.) as ParentLocation,
/*            put(t1.OperationalAreaId,JORA_LOC_BS.) as Bordersection,*/
/*            put(t1.OperationalAreaId,JORA_LOC_MS.) as MemberState,*/
/*            put(t1.OperationalAreaId,JORA_LOC_KBS.) as KeyBorderSection,*/
              t1.DetectedBy,
              t1.InterceptionDate,
              t1.modusoperandi,
              put(datepart(t1.InterceptionDate),YYMMDD10.) as InterceptionDate_day,
              put(timepart(t1.InterceptionDate),time8.) as InterceptionDate_time,
              put(datepart(t1.InterceptionDate),YYMM.) as Interceptiondate_YYYYMM,
              t1.detectiontime,
              put(datepart(t1.detectiontime),YYMMDD10.) as detectiontime_day,
              put(timepart(t1.detectiontime),time8.) as detectiontime_time,
              put(datepart(t1.detectiontime),YYMM.) as detectiontime_YYYYMM,
              t1.InterceptionPlaceComments,
              t1.InterceptedBy,
              t1.CreateDate,
              t1.statusid, /*Status of the incident*/
              put(t1.approverID,Sys_user.) as ApproverID,
              put(datepart(t1.CreateDate),YYMMDD10.) as CreateDate_day,
              put(timepart(t1.CreateDate),time8.) as CreateDate_time,
              put(datepart(t1.CreateDate),YYMM.) as Createdate_YYYYMM,
              put(year(datepart(t1.CreateDate)),4.)||"Wk"||put(week(datepart(t1.CreateDate),'w'),2.) as Createdate_YYYYWK,
              t1.make,
              t1.model,
              put(t1.platenationalityid,Jora_nat.) as PlateNationality,
              put(t1.drivernationalityid,Jora_nat.) as DriverNationality,
              t1.Latitude,
              t1.LatitudeDetection,
              t1.Longitude,
              t1.LongitudeDetection,
              t1.TimeOfDeparture,
              t1.Disembarkation,
              t1.PlaceOfDeparture,
              t1.Comments as IncidentComments,
              t1.DeathCases,
              t1.IncidentNumber,
              t1.MarketValue,
              t1.MigrantsDeterred,
              t1.TransportMeansNumber,
              t1.SearchAndRescueInvolved,
              t1.SmugglingOfNumber,
              put(t1.countryofdepartureID,Jora_nat.) as CountryOfDeparture,
              put(t1.countryofdepartureID,Jora_isocode.) as CountryOfDeparture_iso,
              put(t1.countryofdestinationID,Jora_nat.) as CountryOfDestination,
              put(t1.countryofdestinationID,Jora_isocode.) as CountryOfDestination_iso,
              put(t1.ReferenceId,DICTIONARY.) as RefOperationalArea,
              put(t1.IncidentTypeId,DICTIONARY.) as IncidentType,
              put(t1.BoatDestroyedById,DICTIONARY.) as BoatDestroyedBy,
              put(t1.SmugglingOfId,DICTIONARY.) as SmugglingOf,
              put(t1.PlaceOfInterceptionId ,DICTIONARY.) as PlaceOfInterception,
/*              put(t1.ZoneId ,DICTIONARY.) As Zone,*/
              put(t1.DestinationMethodOfTransportId ,DICTIONARY.) as TransportType,
              /*MigrantInformation Variables*/
              put(t2.outcomeid,DICTIONARY.) as outcome,
              t2.nationalityID,
              t2.nationalityclaimedID,
              t2.nationalitypresumedID,
              put(t2.GenderId ,DICTIONARY.) as Gender,
              t2.age,
              put(t2.ImmigrationStatusId ,DICTIONARY.) as ImmigrationStatusId,
              put(t2.PersonRoleId ,DICTIONARY.) as Mig_PersonRole,
              put(t2.RefusalReasonId ,DICTIONARY.) as Mig_RefusalReason,
              /*PersonDocument Variables*/
              t3.comments as PersonDocComments,
              t3.ExpiringON,
              t3.IssuingDate,
              t3.number as DocNumber,
              t3.typeofdocumentid as TypeOfDocument,
              t3.whereIssued,
              put(t3.Issuingcountryid,Jora_nat.) as IssuingCountry,
              put(t3.TravelDocumentTypeid,DICTIONARY.) as TravelDocumentType,
              put(t3.ExibitedConcealedid,DICTIONARY.) as ExibitedConcealed,
              put(t3.ForgeryTypeId,DICTIONARY.) as ForgeryType,
              put(t3.FoundAtId,DICTIONARY.) as FoundAt,
              put(t3.StatusId,DICTIONARY.) as StatusOfDocId,
              t3.MigrantId,
              put(t3.SupportiveDocumentTypeId,DICTIONARY.) as SupportiveDocumentType,
              put(t3.VisaDocumentTypeId,DICTIONARY.) as VisaDocumentType,
              /*DocumentAlert
              t5.Description as DocAlertDescription,
              t5.RoutingDescription,
              put(t5.GenderId,DICTIONARY.) as DocAlertGender,
              put(t5.NationalityofHolderId,Jora_nat.) as NationalityofHolder,
              put(t5.TravelDestinationId,Jora_nat.) as TravelDestination,
              put(t5.TravelFromId,Jora_nat.) as TravelFrom,
              put(t5.TravelViaId,Jora_nat.) as Travelvia,*/
              1 as NbDoc
          FROM Jorarau.persondocument t3
          left JOIN Jorarau.MigrantInformation t2 ON (t2.Id = t3.MigrantId)
          left join Jorarau.Incident t1 ON (t1.id=t2.IncidentId)
          left JOIN Jorarau.Operation t4 ON (t4.Id=t1.OperationId)
          /*left JOIN Jorarau.documentAlert t5 ON (t5.Id=t3.DocumentAlertId)*/
        Where t1.isdeleted <> 1 and put(t4.operationsectortypeid,DICTIONARY.) in ('LAND','SEA')
        ORDER BY t1.InterceptionDate DESC;

quit;
data work.Jora_PersonDoc;
     length Status $30 TypeofDoc $30;
     set work.jora_Persondoc;
     if statusid=1 then status='Pending';
     if statusid=2 then status='For LCC Verification';
     if statusid=3 then status='For ICC Verification';
     if statusid=4 then status='For FSC Verification';
     if statusid=5 then status='Accepted';

     if typeofdocument=1 then TypeofDoc="Travel";
     if typeofdocument=2 then TypeofDoc="Supportive";
     if typeofdocument=3 then TypeofDoc="Visa";


     if sectortype = "LAND" and Detectiontime=. then
        do;
            IncidentDate_Day= put(datepart(CreateDate),YYMMDD10.);
            IncidentDate_Time= put(datepart(CreateDate),time8.);
            IncidentDate_YYYYMM= put(datepart(CreateDate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(CreateDate)),4.)||"Wk"||put(week(datepart(CreateDate),'w'),2.) ;
        end;
    else if sectortype = "LAND" and Detectiontime <>. then
        do;
            IncidentDate_Day= put(datepart(detectiontime),YYMMDD10.);
            IncidentDate_Time= put(datepart(detectiontime),time8.);
            IncidentDate_YYYYMM= put(datepart(detectiontime),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(detectiontime)),4.)||"Wk"||put(week(datepart(detectiontime),'w'),2.) ;
        end;
    else if sectortype = "SEA" and interceptiondate=. then
        do;
            IncidentDate_Day= put(datepart(CreateDate),YYMMDD10.);
            IncidentDate_Time= put(datepart(CreateDate),time8.);
            IncidentDate_YYYYMM= put(datepart(CreateDate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(CreateDate)),4.)||"Wk"||put(week(datepart(CreateDate),'w'),2.);
        end;
    else if sectortype = "SEA" and interceptiondate<>. then
        do;
            IncidentDate_Day= put(datepart(interceptiondate),YYMMDD10.);
            IncidentDate_Time= put(datepart(interceptiondate),time8.);
            IncidentDate_YYYYMM= put(datepart(interceptiondate),YYMM.);
            IncidentDate_YYYYWK=put(year(datepart(interceptiondate)),4.)||"Wk"||put(week(datepart(interceptiondate),'w'),2.) ;
        end;
         /*"Final" nationality calculation*/
    Nat_confirmed=put(nationalityID,Jora_nat.);
    Nat_claimed=put(nationalityclaimedID,Jora_nat.);
    Nat_presumed=put(nationalitypresumedID,Jora_nat.);

    if strip(nat_confirmed) Not in (".",""," ") then do;
                                                Nat_Final=Nat_confirmed;
                                                Nat_final_iso= put(nationalityID,Jora_isocode.);
                                                end;
        else if strip(Nat_presumed) Not in (".",""," ") then do;
                                                            Nat_Final=Nat_presumed;
                                                            Nat_Final_iso= put(nationalitypresumedID,Jora_isocode.);
                                                            end;

                else if strip(Nat_claimed) Not in (".",""," ") then do;
                                                            Nat_Final=Nat_claimed;
                                                            Nat_Final_iso=put(nationalityclaimedID,Jora_isocode.);
                                                            end;
            /*end nationnality computation*/

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

if datepart(IncidentDate)="31dec2012"d then IncidentDate_YYYYWK="2013Wk 1";
if datepart(IncidentDate)="01jan2012"d then IncidentDate_YYYYWK="2011Wk52";

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

if datepart(IncidentDate)="30dec2013"d then IncidentDate_YYYYWK="2014Wk 1";
if datepart(IncidentDate)="31dec2013"d then IncidentDate_YYYYWK="2014Wk 1";


drop TypeofDocument;
drop statusid;
run;

proc sql;
    create table tmp_BS as
    select t2.id as IncidentId, IncidentNumber,
    case when missing(t4.CountryId) NE 0 then "" else strip(put(t4.CountryId,fmt_country_long.)) end as MemberState,
    case when missing(t4.BorderSectionId) NE 0 then "" else strip(put(t4.BorderSectionId,fmt_country_long.)) end as BorderSection,
    /* BorderSection */
    (case when missing(t4.CountryId) NE 0 then "" else put(t4.CountryId,   fmt_country_short.) end || case when missing(t4.CountryId) NE 0 or missing(t4.BorderSectionId) NE 0 then ""
    else " - " end || case when missing(t4.BorderSectionId) NE 0 then "" else put(t4.BorderSectionId,   fmt_country_short.) end) LABEL="Key border section" AS KeyBorderSection,

    /* OperationalArea */   %FormatValue(t2.ZoneId, fmt_joraDict) LABEL="Operational area" AS OperationalArea,
    /* ReportingUnit */     %FormatValue(t3.ReportingLocationId, fmt_joraDict) LABEL="Reporting unit" AS ReportingUnit
    FROM JORARAU.Operation t1
        RIGHT JOIN JORARAU.Incident t2 ON (t1.Id = t2.OperationId)
        LEFT JOIN JORARAU.Location t3 ON (t2.OperationalAreaId = t3.Id)
        INNER JOIN JORARAU.Dictionary t4 ON (t3.ReportingLocationId = t4.Id)
    ;
quit;

proc sql;
    create table shredstg.jora_mig2 as
    select * from work.jora_mig_temp/*(drop=Memberstate Bordersection Keybordersection)*/ as t1
    left outer join tmp_BS as t2
    on (t1.IncidentNumber=t2.IncidentNumber);

    create table shredstg.Jora_PersonDoc2 as
    select * from work.Jora_PersonDoc/*(drop=Memberstate Bordersection Keybordersection)*/ as t1
    left outer join tmp_BS as t2
    on (t1.IncidentNumber=t2.IncidentNumber);
quit;

*LOCK SHREDSTG.JORA_MIG clear;

proc datasets lib=work nolist nowarn;
    delete tmp_: test_: fmt_: dic_: jora_: location;
quit;