Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement