I need to convert SAS script to SQL, please assist.
proc sql;
create table Entity as
select *
from goPRD.t_entity
where
UPCASE(TEN_NAME) LIKE 'T&R FAMILY%' OR
compress(ten_incorporation_number,'/\ABCDEFGHIJLNOPQRSTUVW')='37632013'
;
quit;
/*FIC GLOBAL*/
proc sql;
create table ENT_FICGL as
SELECT *
FROM FICGL.MAAGeo
where compress(InstitutionRegistrationNumber,'/\ABCDEFGHIJLNOPQRSTUVW') ='37632013' OR
upcase(ReportedCompanyName) like 'T&R FAMILY%'
;
quit;
PROC EXPORT DATA = ENT_FICGL
OUTFILE = "C:\SAS\MIRQT-220816-0000009\%sysfunc(date(),YYMMDD10.) - FICGLOBAL - Entities_STR_CTR.xlsx"
DBMS = XLSX
REPLACE;
SHEET = 'Detailed';
RUN;
proc sql;
create table signatory as
select b.tac_account_id,
a.*
from Entity a left join goPRD.t_account b
on a.ten_entity_id =b.tac_entity_id
;
quit;
data _ent;
set entity;
tac_account_id=ten_entity_id;
format Signatory $5.;
Signatory = "No";
run;
data signatory;
set signatory _Ent;
if Signatory = "" then Signatory="Yes";
if tac_account_id ne .;
run;
options mlogic mprint mgen;
%macro TRANS() ;
data Persons_(keep=tac_account_id);
set signatory;
if tac_account_id ne .;
run;
data _null_;
set Persons_ end=eof;
n=_n_;
call symput("PR_Num"||compress(n),compress(tac_account_id));
if eof then call symput('nobs',_n_);
run;
/*Check if dataset is EMPTY or not*/
/*If dataset is empty, process will stop*/
proc sql noprint;
select count(*) into :observations from Persons_;
quit;
/*If it is empty*/
%if &observations. = 0 %then %do;
data _NULL_;
tac_account_id=0;
n=_n_;
call symput("PR_Num"||compress(n),compress(tac_account_id));
call symput('nobs',_n_);
run;
%end;
/*Destination*/
PROC SQL;
CREATE TABLE Sec_28_trans_dest AS
SELECT ttr_transaction_id
,ttr_transaction_number
,ttr_amount_local
,ttr_report_type
,ttr_source_party_id
,ttr_dest_party_id
,ttr_location
,ttr_date_transaction
,ttr_source_party_type
,ttr_dest_party_type
,ttr_source_country
,ttr_dest_country
FROM goPRD.t_transaction
WHERE
ttr_dest_party_id
IN
(
%do i=1 %to &nobs.;
&&PR_Num&i.
%end;
)
;
QUIT;
/*Source*/
PROC SQL;
CREATE TABLE Sec_28_trans_sour AS
SELECT ttr_transaction_id
,ttr_transaction_number
,ttr_amount_local
,ttr_report_type
,ttr_source_party_id
,ttr_dest_party_id
,ttr_location
,ttr_date_transaction
,ttr_source_party_type
,ttr_dest_party_type
,ttr_source_country
,ttr_dest_country
FROM goPRD.t_transaction
WHERE
ttr_source_party_id
IN
(
%do i=1 %to &nobs.;
&&PR_Num&i.
%end;
)
;
QUIT;
%mend TRANS;
%TRANS();
data sec_28_trns;
set Sec_28_trans_dest Sec_28_trans_sour;
run;
proc sql;
create table get_ent as
select a.ten_incorporation_number,
a.ten_entity_id,
a.ten_name,
a.Signatory,
b.*
from Signatory a right join sec_28_trns b
on a.tac_account_id = b.ttr_source_party_id
or a.tac_account_id = b.ttr_dest_party_id
;
quit;
/*Check if dataset is EMPTY or not*/
/*If dataset is empty, process will stop*/
proc sql noprint;
select count(*) into :observations from sec_28_trns;
quit;
/*If it is empty*/
%if &observations. = 0 %then %do;
PROC EXPORT DATA = sec_28_trns
OUTFILE = "&folder.\%sysfunc(date(),YYMMDD10.) - &report_ref. Results - Entity_STR_CTR.xlsx"
DBMS = XLSX
REPLACE;
/* SHEET = 'Name_DOB_Match';*/
SHEET = 'Detailed data';
RUN;
PROC PRINTTO LOG = LOG;
RUN;
%end;
/*Continue if dataset not empty.*/
%if &observations. >= 1 %then %do;
%macro REPID() ;
data _null_;
set sec_28_trns end=eof;
n=_n_;
call symput("TR_ID"||compress(n),compress(ttr_transaction_id));
call symput('nobs',_n_);
run;
/*Check if dataset is EMPTY or not*/
/*If dataset is empty, process will stop*/
proc sql noprint;
select count(*) into :observations from sec_28_trns;
quit;
/*If it is empty*/
%if &observations. = 0 %then %do;
data _NULL_;
ttr_transaction_id=0;
n=_n_;
call symput("TR_ID"||compress(n),compress(ttr_transaction_id));
run;
%end;
/*Destination*/
PROC SQL;
CREATE TABLE t_rep_party_ID AS
SELECT trt_transaction_id
,trt_report_id
FROM goPRD.t_report_transaction
WHERE
trt_transaction_id
IN
(
%do i=1 %to &nobs.;
&&TR_ID&i.
%end;
)
;
QUIT;
/*GET REPORT*/
data _null_;
set T_rep_party_id end=eof;
n=_n_;
call symput("TR_REP"||compress(n),compress(trt_report_id));
if eof then call symput('nobs',_n_);
run;
/*Check if dataset is EMPTY or not*/
/*If dataset is empty, process will stop*/
proc sql noprint;
select count(*) into :observations from T_rep_party_id;
quit;
/*If it is empty*/
%if &observations. = 0 %then %do;
data _NULL_;
trt_report_id=0;
n=_n_;
call symput("TR_REP"||compress(n),compress(trt_report_id));
call symput('nobs',_n_);
run;
%end;
/*Destination*/
PROC SQL;
CREATE TABLE t_rep_party_REP AS
SELECT tre_report_id,
tre_ref_number,
tre_reason,
tre_rentity_id,
tre_date
FROM goPRD.t_report
WHERE
tre_report_id
IN
(
%do i=1 %to &nobs.;
&&TR_REP&i.
%end;
)
;
QUIT;
%mend REPID;
%REPID();
proc sql;
create table t_reps as
select a.*,
b.*
from T_rep_party_id a inner join T_rep_party_rep b
on a.trt_report_id=b.tre_report_id
;
quit;
proc sql;
create table sec28_rep as
select a.*,
b.*
from get_ent a inner join t_reps b
on a.ttr_transaction_id=b.trt_transaction_id
;
quit;
/*merge back*/
/*Get Banks*/
proc sql;
create table banks as
select *
from sqleft.Banks_information;
quit;
/*Get Account information - Source*/
proc sql;
create table get_acc_source as
select a.*,
b.tac_account as Source_Account,
b.tac_bank_id as Source_Bank
from sec28_rep a left join goPRD.t_account b
on a.ttr_source_party_id = b.tac_account_id
;
quit;
proc sql;
create table get_acc_source as
select a.*,
b.tac_institution_name as Source_Bank_Name
from get_acc_source a left join banks b
on a.Source_Bank = b.tac_bank_id
;
quit;
/*Get Account information - Destination*/
proc sql;
create table get_acc_source as
select a.*,
b.tac_account as Destination_Account,
b.tac_bank_id as Destination_Bank
from get_acc_source a left join goPRD.t_account b
on a.ttr_dest_party_id = b.tac_account_id
;
quit;
proc sql;
create table get_acc_source as
select a.*,
b.tac_institution_name as Destination_Bank_Name
from get_acc_source a left join banks b
on a.Destination_Bank = b.tac_bank_id
;
quit;
/*Get reporter.*/
proc sql;
create table Report_reporter as
select a.*,
b.AI_RI_Number,
b.Business_Type_Group,
b.Agency_Name
from get_acc_source a left join sqleft.goAML_AI_RI b
on a.tre_rentity_id =b.goAML_Entity_ID
where ttr_report_type not in ("IRD" "AIF")
;
quit;
proc sql;
create table report_ind as
select a.*,
b.tri_indicator
from Report_reporter a left join goPRD.t_report_indicator b
on a.tre_report_id=b.tri_report_id
;
quit;
proc sort data=report_ind nodupkey;by tre_report_id trt_transaction_id;
run;
proc sql;
create table report_indicator as
select a.*,b.lk_name
from report_ind a left join sqleft.REF_REPORT_INDICATOR_20181012 b
on a.tri_indicator=b.lk_code
;
quit;
data FINAL_SEC28_ENTITY(drop= ten_entity_id ttr_transaction_id ttr_source_party_id ttr_dest_party_id trt_report_id Source_Bank Destination_Bank tre_report_id trt_transaction_id tpe_person_id
tpe_identification_no tri_indicator tre_rentity_id);
format ttr_source_party_type $20.;
format ttr_dest_party_type $20.;
set report_indicator;
if ttr_source_party_type = "E" then ttr_source_party_type = "Entity";
else if ttr_source_party_type = "P" then ttr_source_party_type = "Person";
else if ttr_source_party_type = "A" then ttr_source_party_type = "Account";
if ttr_dest_party_type = "E" then ttr_dest_party_type = "Entity";
else if ttr_dest_party_type = "P" then ttr_dest_party_type = "Person";
else if ttr_dest_party_type = "A" then ttr_dest_party_type = "Account";
/*ADD date for SAR*/
if ttr_date_transaction = . then ttr_date_transaction=tre_date;
ten_incorporation_number = compress(ten_incorporation_number,'/');
rename ten_name = Entity_Name;
rename ten_incorporation_number = Entity_Registration;
rename ttr_transaction_number = Transaction_No;
rename ttr_amount_local = Transaction_Amount;
rename ttr_report_type = Report_Type;
rename ttr_location = Transaction_Location;
rename ttr_date_transaction = Tranaction_Date;
rename tre_ref_number = Report_Ref;
rename tre_reason = Reason;
rename AI_RI_Number = Agency_Reg;
rename Business_Type_Group = Business_Group;
rename lk_name = Indicator;
rename ttr_source_party_type = Source_Party_Type;
rename ttr_dest_party_type = Destination_Party_Type;
rename ttr_source_country = Source_Country;
rename ttr_dest_country = Destination_Country;
run;
data FINAL_SEC28_ENTITY;
retain Entity_Registration Entity_Name Signatory Report_Type Report_Ref Transaction_Amount Transaction_No Tranaction_Date Indicator Reason Agency_Reg Agency_Name Business_Group;
set FINAL_SEC28_ENTITY;
run;
/*remove resubmitted information*/
proc sort data=FINAL_SEC28_ENTITY;by Transaction_No Transaction_Amount descending Report_Ref;
run;
proc sort data=FINAL_SEC28_ENTITY nodupkey;by Transaction_No Transaction_Amount;
run;
/*PLOT THE REPORT*/
proc sql;
create table EN_REP as
select Entity_Registration,Report_Type,sum(Transaction_Amount) as TOTAL_VALUE
from FINAL_SEC28_ENTITY
group by 1,2
;
quit;
proc transpose data=EN_REP out=trns_en;
by Entity_Registration;
id Report_Type;
quit;
/*REMOVE DIFFERENT ENTITY NAMES*/
data _entities(keep=Entity_Registration Entity_Name);
set FINAL_SEC28_ENTITY;
run;
proc sort data=_entities nodupkey;by Entity_Registration;
run;
/*combine transposed data with entities to get entity name*/
proc sql;
create table summary_entities as
select a.*,
b.Entity_Name
from trns_en a left join _entities b
on a.Entity_Registration = b.Entity_Registration
;
quit;
data summary_entities(drop=_NAME_);
retain Entity_Registration Entity_Name;
set summary_entities;
run;
PROC PRINTTO LOG = "&folder.\Temp\Temp_Log.txt";
RUN;
PROC EXPORT DATA = summary_entities
OUTFILE = "C:\SAS\MIRQT-220816-0000009\%sysfunc(date(),YYMMDD10.) - goAML - Entities_STR_CTR.xlsx"
DBMS = XLSX
REPLACE;
SHEET = 'summary';
RUN;
PROC EXPORT DATA = FINAL_SEC28_ENTITY
OUTFILE = "C:\SAS\MIRQT-220816-0000009\%sysfunc(date(),YYMMDD10.) - goAML - Entities_STR_CTR.xlsx"
DBMS = XLSX
REPLACE;
SHEET = 'Detailed';
RUN;
