Thankyou for your reply.I tried adding format to count_level and will paste the before and after images of the results Meanwhile could you please check the code again. /*Count CVM Account Numbers*/
proc sql noprint;
create table cvm_account_number_cnt_&alert_month. as
SELECT &cvia_extrctdt. as PERIOD_DT
,'ACCT' AS COUNT_LEVEL
,COUNT(Account_Num) AS CNT
FROM cvm_account_number_&alert_month.
GROUP BY 1,2
;
quit;
data cvm1.cvm_acc_number_cnt;
set cvm_account_number_cnt_&alert_month.;
run;
/* =============================================================================*/
/*Count CVM Transactions*/
proc sql noprint;
connect to teradata (server="&mr_td_srv" user="&mr_usr" password="&mr_pw" BULKLOAD=no mode=teradata &QB.);
create table cvm_transaction_cnt_&alert_month. as select * from connection to teradata
(select &cvia_extrctdt. as PERIOD_DT
,'TRAN' AS COUNT_LEVEL
,sum(CASE WHEN Account_Num ne '' THEN 1 ELSE 0 END) as CNT
from &ADWDM_FIU_V1..CVIA_TH
group by 1,2
where period_dt = &cvia_extrctdt.
)%TD_ISSUE;
disconnect from teradata;
quit;
/* =============================================================================*/
/*Count CTR Exemption records at TIN level*/
proc sql noprint;
connect to teradata (server="&mr_td_srv" user="&mr_usr" password="&mr_pw" BULKLOAD=no mode=teradata &QB.);
create table CVM_TB_CTR_EXMP_1&yymm. as select * from connection to teradata
(
SELECT DISTINCT
TIN
FROM &P_FCA_AML_U_WORK_03..TB_CTR_EXMP_1&yymm.
)%TD_ISSUE;
disconnect from teradata;
quit;
proc sql noprint;
create table CVM_TB_CTR_EXMP_TIN_CNT_1&yymm. as
select
&cvia_extrctdt. as PERIOD_DT
,'EXMP' AS COUNT_LEVEL
,count(*) as CNT
from CVM_TB_CTR_EXMP_1&yymm.
group by 1,2
;
quit;
/* =============================================================================*/
/*Combine all counts*/
%MACRO EXIST_CVM_CNT;
%if %sysfunc(exist(cvm_account_number_cnt_&alert_month.)) %then %do;cvm_account_number_cnt_&alert_month. %end;
%if %sysfunc(exist(cvm_transaction_cnt_&alert_month.)) %then %do;cvm_transaction_cnt_&alert_month. %end;
%if %sysfunc(exist(CVM_TB_CTR_EXMP_TIN_CNT_1&yymm.)) %then %do;CVM_TB_CTR_EXMP_TIN_CNT_1&yymm. %end;
%if %sysfunc(exist(cvm_cust_cnt_&alert_month.)) %then %do;cvm_cust_cnt_&alert_month. %end;
%MEND;
/*APPEND TOGETHER ALL CUSTOMERS AND ACCOUNTS*/
DATA CVM_CUST_ACCT_TRAN_&Alert_Month.;
SET %EXIST_CVM_CNT;
RUN;
data cvm1.CUST_ACCT_TRAN_&Alert_Month.;
set CVM_CUST_ACCT_TRAN_&Alert_Month.;
run;
/* =============================================================================*/
/*Export counts to excel file*/
proc export
data=CVM_CUST_ACCT_TRAN_&Alert_Month.
dbms=xlsx
outfile="&CVM_Control./CVM_CUST_ACCT_TRAN_&Alert_Month..xlsx"
replace;
run;
%macro FormatMacroVar(value,format);
%if %datatyp(&value)=CHAR
%then %sysfunc(putc(&value,&format));
%else %left(%qsysfunc(putn(&value,&format)));
%mend FormatMacroVar;
%MACRO PULL_CVM_TRAN_CNT(MONTH_CHAR, MONTH_NUM);
%LET TAG = Cash_Vault_Monitoring_Summary;
PROC IMPORT DATAFILE="&CVM_Control./CVM_CUST_ACCT_TRAN_&MONTH_NUM..xlsx"
DBMS = XLSX
OUT = CVM_CUST_ACCT_TRAN_&MONTH_NUM.
REPLACE;
SHEET = "CVM_CUST_ACCT_TRAN_&MONTH_NUM.";
RUN;
PROC SQL;
CREATE TABLE CVM_TRAN_CNT_&MONTH_NUM. AS
SELECT
&MONTH_NUM. AS PERIOD_DT,
COUNT_LEVEL,
CNT
FROM CVM_CUST_ACCT_TRAN_&MONTH_NUM.
QUIT;
%MEND PULL_CVM_TRAN_CNT;
%PULL_CVM_TRAN_CNT(&month0_chr.,&month0.);
%PULL_CVM_TRAN_CNT(&month1_chr.,&month1.);
%PULL_CVM_TRAN_CNT(&month2_chr.,&month2.);
%PULL_CVM_TRAN_CNT(&month3_chr.,&month3.);
%PULL_CVM_TRAN_CNT(&month4_chr.,&month4.);
%PULL_CVM_TRAN_CNT(&month5_chr.,&month5.);
%PULL_CVM_TRAN_CNT(&month6_chr.,&month6.);
%PULL_CVM_TRAN_CNT(&month7_chr.,&month7.);
%PULL_CVM_TRAN_CNT(&month8_chr.,&month8.);
%PULL_CVM_TRAN_CNT(&month9_chr.,&month9.);
%PULL_CVM_TRAN_CNT(&month10_chr.,&month10.);
%PULL_CVM_TRAN_CNT(&month11_chr.,&month11.);
%PULL_CVM_TRAN_CNT(&month12_chr.,&month12.);
%macro ExistCVMCounts();
%if %sysfunc(exist(CVM_TRAN_CNT_&month0.)) %then %do;CVM_TRAN_CNT_&month0. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month1.)) %then %do;CVM_TRAN_CNT_&month1. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month2.)) %then %do;CVM_TRAN_CNT_&month2. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month3.)) %then %do;CVM_TRAN_CNT_&month3. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month4.)) %then %do;CVM_TRAN_CNT_&month4. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month5.)) %then %do;CVM_TRAN_CNT_&month5. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month6.)) %then %do;CVM_TRAN_CNT_&month6. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month7.)) %then %do;CVM_TRAN_CNT_&month7. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month8.)) %then %do;CVM_TRAN_CNT_&month8. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month9.)) %then %do;CVM_TRAN_CNT_&month9. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month10.)) %then %do;CVM_TRAN_CNT_&month10. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month11.)) %then %do;CVM_TRAN_CNT_&month11. %end;
%if %sysfunc(exist(CVM_TRAN_CNT_&month12.)) %then %do;CVM_TRAN_CNT_&month12. %end;
%mend ExistCVMCounts;
DATA CVM_TRAN_CNT_SUMMARY_0;
SET %ExistCVMCounts;
RUN;
data cvm1.cvm_tran_summary_0;
set CVM_TRAN_CNT_SUMMARY_0;
run;
PROC IMPORT
DATAFILE="&CVM_Control./CVM_INPUT_PRECHECK_KPIs.xlsx"
OUT = CVM_INPUT_PRECHECK_KPIs
DBMS=xlsx
REPLACE;
SHEET="CVM_LIMITS";
GETNAMES=YES;
RUN;
PROC SQL NOPRINT;
CREATE TABLE cvm1.CVM_TRAN_CNT_SUMMARY_S AS
SELECT *
FROM CVM_TRAN_CNT_SUMMARY_0
ORDER BY COUNT_LEVEL, PERIOD_DT;
QUIT;
PROC SQL NOPRINT;
CREATE TABLE CVM_TRAN_CNT_SUMMARY_S AS
SELECT *
FROM CVM_TRAN_CNT_SUMMARY_0
ORDER BY COUNT_LEVEL, PERIOD_DT;
QUIT;
proc sql;
create table cvm1.CVM_TRAN_CNT_SUMMARY_S as
select distinct PERIOD_DT,COUNT_LEVEL,CNT FROM CVM_TRAN_CNT_SUMMARY_S;
quit;
proc sort data=cvm1.CVM_TRAN_CNT_SUMMARY_S ;
by COUNT_LEVEL;
run;
data cvm1.CVM_TRAN_CNT_SUMMARY_S;
SET cvm1.CVM_TRAN_CNT_SUMMARY_S;
FORMAT COUNT_LEVEL $3.;
RUN;
PROC TRANSPOSE DATA = cvm1.CVM_TRAN_CNT_SUMMARY_S
OUT = CVM_TRAN_CNT_SUMMARY_T(DROP=_name_ _LABEL_)
prefix = M_;
BY COUNT_LEVEL;
ID PERIOD_DT ;
VAR CNT ;
RUN;
data cvm1.CVM_TRAN_CNT_SUMMARY_T;
set CVM_TRAN_CNT_SUMMARY_T;
run;
PROC SORT DATA=cvm1.CVM_TRAN_CNT_SUMMARY_S;
BY PERIOD_DT;
RUN;
PROC TRANSPOSE DATA =cvm1.CVM_TRAN_CNT_SUMMARY_S
OUT =CVM_TRAN_CNT_SUMMARY_P(DROP=_name_ _LABEL_);
BY PERIOD_DT;
ID COUNT_LEVEL ;
VAR CNT ;
RUN;
data cvm1.CVM_TRAN_CNT_SUMMARY_P;
set CVM_TRAN_CNT_SUMMARY_P;
run;
PROC SQL NOPRINT;
CREATE TABLE CVM_TRAN_CNT_SUMMARY_&month0. AS
SELECT a.*
, b.OCLB
, b.ICLB
, b.MEDIAN
, b.ICUB
, b.OCUB
FROM CVM_TRAN_CNT_SUMMARY_T a
INNER JOIN CVM_INPUT_PRECHECK_KPIs b
ON b.COUNT_LEVEL = a.COUNT_LEVEL;
QUIT;
ods _all_ close;
ods excel file = "&CVM_Rephist./CVM_TRAN_CNT_Summary_&month0..xlsx" options(sheet_name="TRAN_CNT_&month0.");
proc report data=CVM_TRAN_CNT_SUMMARY_&month0. nowd;
column COUNT_LEVEL M_&MONTH12. M_&MONTH11. M_&MONTH10. M_&MONTH9. M_&MONTH8. M_&MONTH7. M_&MONTH6. M_&MONTH5. M_&MONTH4. M_&MONTH3. M_&MONTH2. M_&MONTH1. OCLB ICLB MEDIAN ICUB OCUB M_&MONTH0.;
define COUNT_LEVEL / GROUP;
define M_&month12. / display format=comma18.;
define M_&month11. / display format=comma18.;
define M_&month10. / display format=comma18.;
define M_&month9. / display format=comma18.;
define M_&month8. / display format=comma18.;
define M_&month7. / display format=comma18.;
define M_&month6. / display format=comma18.;
define M_&month5. / display format=comma18.;
define M_&month4. / display format=comma18.;
define M_&month3. / display format=comma18.;
define M_&month2. / display format=comma18.;
define M_&month1. / display format=comma18.;
define OCLB / display format=comma18.;
define ICLB /display format=comma18.;
define MEDIAN /display format=comma18.;
define ICUB /display format=comma18.;
define OCUB /display format=comma18.;
define M_&month0. / display format=comma18.;
compute M_&month0.;
if round(M_&month0.) >= round(ICLB) and round(M_&month0.) <= round(ICUB) then call define(_col_,"style","style={background=lightGREEN}");
else if (round(M_&month0.) >= round(OCLB) and round(M_&month0.) <= round(ICLB)) or (round(M_&month0.) >= round(ICUB) and round(M_&month0.) <= round(OCUB)) then call define(_col_,"style","style={background=yellow}");
else if round(M_&month0.) < round(OCLB) or round(M_&month0.) > round(OCUB) then call define(_col_,"style","style={background=red}");
else call define(_col_,"style","style={background=white}");
endcomp;
run;
ods excel close;
ods listing;
... View more
