Convert SAS Proc script to SQL

[Lindilethabo]

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;

[Kurt_Bremser]

Which of the steps do you want to have as SQL code?

[LinusH]

It look like you want us to do your job for you.

First of all, why?

I suggest you have a go yourself and return to us with specific problems, not a whole work task.

[vijaypratap0195]

😂😂

[Tom]

Most of that looks like it already is written in SQL.  Did you instead mean you want to translate it to run in some specific implementation of SQL?  If so which one?  What did you try?  It what ways did it not work?

[AlanC]

First of all, SAS doesn't directly translate to SQL. They are different languages and different structures. Every step needs to be redone. Some SQL appears to be there but it is SAS SQL (semi-related to ANSI but its own animal). 

SAS language conversion is hard. Take it one step boundary at a time. Translate, test, see if the results match. However, SAS will not translate all the way to SQL. SAS can translate between languages (ex. Python, C#) but it is very, very hard work. SQL does not have the capabilities to handle all of SAS' constructs. You have macros in there making it even more enjoyable. Good luck.