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;
... View more