options compress=yes sumsize=1024m sortsize=1024m;
%let node=10.2.147.252 7551;
options comamid=tcp remote=node;
filename rlink "D:\Program Files\SAS\SAS 9.1\connect\saslink\tcpunixnew.scr";
signon node;
libname ods "/sasdata/ods_card" server=node;
libname bam_new "/sasmid/bam" server=node;
signoff _all_;
libname dq "F:\zhichan02\地区风险分析_sjl";
rsubmit node;
libname bam_new "/sasmid/bam";
libname ods "/sasdata/ods_card";
options compress=yes;
/**1、账户**/
data Bam_acct_info1505;
set bam_new.Bam_acct_info1505(keep=stat_date bank_nbr AGT_NUM AGT_MODIF_NUM brch_nbr CUST_NUM LOCK_CODE LOCK_CODE_REASON_CD
PROD_ID FOREV_CONSM_CRDT_FEE bank_area CRDACCT_DLQ crdacct_rece_bal mord_unpay_bal crdacct_effi pub_channel);
run; /***增加 分期未入张mord_unpay_bal*****/
proc download data=Bam_acct_info1505;run;
endrsubmit;
rsubmit node;
libname bam_new "/sasmid/bam";
libname ods "/sasdata/ods_card";
options compress=yes;
/**2、卡片**/
data Bam_card_info1505;
set bam_new.Bam_card_info1505(keep=CARD_NUM M_AGT_NUM M_AGT_MODIF_NUM
CUST_NBR CARD_LEVEL_CD OPEN_CARD_DT appm_source); /**取通路代码是否同时要取决定**/
if CARD_LEVEL_CD="@00" then CARD_LEVEL_CD="99";
rename M_AGT_NUM=AGT_NUM M_AGT_MODIF_NUM=AGT_MODIF_NUM; /****对基本账户协议修饰符、基本账户协议编号进行重命名之后再拼表****/
run;
proc download data=Bam_card_info1505;run;
endrsubmit;
data Bam_card_info1505;
set Bam_card_info1505;
crdacct_nbr=compress (AGT_NUM||AGT_MODIF_NUM);/**把协议修饰符与协议编号合并成一个字符再进行去重*****/
run;
proc sort data=Bam_card_info1505;by crdacct_nbr OPEN_CARD_DT CARD_LEVEL_CD;run;
data Bam_card_info1505_1;
set Bam_card_info1505;
by crdacct_nbr OPEN_CARD_DT CARD_LEVEL_CD;
if last.crdacct_nbr;
run;
/*******无纸化不变，逻辑库名待确认，但只能用卡号与其他数据集进行关联，故先在卡片层进行关联***************/
/**************************************提取客户申请基本信息******************************************/
rsubmit node;
libname bam_new "/sasmid/bam";
libname ods "/sasdata/ods_card";
options compress=yes;
data Gdb_base_approve; /***审批信息表*****/
set ods.Gdb_base_approve(keep=GDB_NBR APPROVE_RESULT MAIN_CARD_CREDIT CUST_NBR
VENTUREMARK CARD_ID MAIN_CARD_CREDIT AUDIT_TIME); /**去掉账户号**/
if APPROVE_RESULT="A";
run;
data Gdb_base_ftmess; /***初审信息*****/
set ods.Gdb_base_ftmess(keep=GDB_NBR CALLING_ID KHQDM);
run;
data Gdb_base_bank; /***银行信息*****/
set ods.Gdb_base_bank(keep=GDB_NBR STAFF_NAME STAFF_ID);
run;
proc sort data=Gdb_base_approve;by GDB_NBR;run;
proc sort data=Gdb_base_ftmess;by GDB_NBR;run;
proc sort data=Gdb_base_bank;by GDB_NBR;run;
data Gdb_base;
merge Gdb_base_approve Gdb_base_ftmess Gdb_base_bank;
by GDB_NBR;
run;
proc download
data=Gdb_base;run;
endrsubmit;
data dq.Gdb_base;
set Gdb_base;
if APPROVE_RESULT="A";
bank_nbr=substr(CARD_ID,8,2)+0;
CARD_NUM=CARD_ID;
drop CARD_ID;
run;
data dq.Gdb_base;
set dq.Gdb_base;
if STAFF_ID^="" then STAFF_ID1=STAFF_ID;
else if STAFF_ID="" then STAFF_ID1=STAFF_NAME;
run;
data dq.Gdb_base;
set dq.Gdb_base;
g=prxparse('s/[^a-zA-Z0-9]//');
call prxchange(g,-1,STAFF_ID1);
run;
data Gdb_base;
set dq.Gdb_base(keep=GDB_NBR CARD_NUM MAIN_CARD_CREDIT VENTUREMARK CALLING_ID KHQDM
bank_nbr STAFF_ID1 AUDIT_TIME);
run;
/***将卡表与无纸化数据合并***/
proc sort data=Gdb_base;by CARD_NUM;run;
proc sort data=Bam_card_info1505_1;by CARD_NUM;run;
data Bam_card_info1505_1;
merge Bam_card_info1505_1(in=a) Gdb_base;
by CARD_NUM;
if a;
run;
/***需进一步去重*****/
proc sort data=Bam_card_info1505_1;by CRDACCT_NBR ;run;
data Bam_card_info1505_1;
set Bam_card_info1505_1;
by CRDACCT_NBR;
if last.CRDACCT_NBR;
run;
/**3、持卡人**/
rsubmit node;
libname bam_new "/sasmid/bam";
libname ods "/sasdata/ods_card";
options compress=yes;
data Bam_cust_info1505;
set bam_new.Bam_cust_info1505(keep=BANK_NBR CUST_NUM YEAR_TAX_PRE_SALARY CUST_INDUSTRY_CODE CUST_QUALIFICATION
CUST_BUS_CODE CUST_POS_CODE cust_age cust_group);
run;
/******************区分AB+、企业主——增加项目*****************/
data Cust_abplus1505;
set bam_new.Cust_abplus1505 ;
run;
proc download data=Bam_cust_info1505;run;
proc download data=Cust_abplus1505;run;
endrsubmit;
proc sort data=Cust_abplus1505;by CUST_NUM Cust_ABplus;run;
data Cust_abplus1505;
set Cust_abplus1505;
by CUST_NUM Cust_ABplus;
if last.Cust_ABplus;
run;
/****企业主与人进行合并*******/
proc sort data=Bam_cust_info1505;by CUST_NUM ;run;
data Bam_cust_info1505;
merge Bam_cust_info1505(in=a) Cust_abplus1505;
by CUST_NUM ;
if a;
run;
/*****需进一步按客户号去重****/
proc sort data=Bam_cust_info1505;by CUST_NUM ;run;
data Bam_cust_info1505;
set Bam_cust_info1505;
by CUST_NUM;
if last.CUST_NUM;
run;
/*****卡户进行合并****/
proc sort data=Bam_acct_info1505;by AGT_NUM AGT_MODIF_NUM;run;
proc sort data=Bam_card_info1505_1;by AGT_NUM AGT_MODIF_NUM;run;
data acct_card;
merge Bam_acct_info1505(in=a) Bam_card_info1505_2;
by AGT_NUM AGT_MODIF_NUM;
if a;
run;
/*****卡户人进行合并****/
proc sort data=acct_card;by CUST_NUM;run;
proc sort data=Bam_cust_info1505;by CUST_NUM;run;
data dq.acct_card_cust1505_cs;
merge acct_card(in=a) Bam_cust_info1505;
by CUST_NUM;
if a;
run;
/***底层数据由EDW推送过来****/
rsubmit node;
libname bam_new "/sasmid/bam";
libname ods "/sasdata/ods_card";
options compress=yes;
data cust_add_1;
set ods.CM_CRDT_CARD_CARD_HOLDER_INFO(keep= CUST_NUM WORK_CORP MARRIAGE_STATUS_CD CAREER_TYPE_CD) ;
run;
data cust_add_2;
set ods.CM_CRDT_CAD_
CAD_HOLDER_ADDR_IF(keep= CUST_NUM CORP_ADDR RPR_ADDR GUARTOR_ADDR) ;
run;
proc sort data=cust_add_1;by CUST_NUM;run;
proc sort data=cust_add_2;by CUST_NUM;run;
proc download data=cust_add_1;run;
proc download data=cust_add_2;run;
endrsubmit;
data cust_add;
merge cust_add_1(in=a) cust_add_2;
by CUST_NUM;
if a;
run;
data dq.acct_card_cust1505_cs;
merge dq.acct_card_cust1505_cs(in=a) cust_add;
by CUST_NUM;
if a;
run;
/***合并持卡人单位名称与地址*****/
data dq.acct_card_cust1505_cs;
set dq.acct_card_cust1505_cs;
add_dw=compress(WORK_CORP||CORP_ADDR);
drop CORP_ADDR;
run;
data dq.acct_card_cust1505_cs;
set dq.acct_card_cust1505_cs;
if STAFF_ID1="" then STAFF_ID1=BRCH_NBR;
else STAFF_ID1=STAFF_ID1;
run;
/****增加渠道项目*****/
data P_channel;
set bam_new.P_channel(keep=pub_channel channel_desc_1 channel_desc_2 channel_desc_3 channel_desc_4);
run;
proc sort data=P_channel; by pub_channel;run;
proc sort data= dq.acct_card_cust1505_cs;by pub_channel;run;
data dq.acct_card_cust1505_cs_1;
merge dq.acct_card_cust1505_cs(in=a) P_channel ;
by pub_channel;
if a;
run;
data dq.acct_card_cust1505_cs_2; /****MAIN_CARD_CREDIT在无纸化系统提取，故在划分卡等级的时候，是否会有一些问题？*****/
set dq.acct_card_cust1505_cs_1;
if CARD_LEVEL_CD="4" and 0
else card_grade1=CARD_LEVEL_CD;
run;
data dq.acct_card_cust1505_cs_2; /****KHQD在无纸化系统取得，无纸化之前的客户客群无从得知***/
set dq.acct_card_cust1505_cs_2;
format kq $16.;
if datepart(AUDIT_TIME)<"01jul2009"d then kq="无纸化前";
else if PROD_NBR in ("907","908","917","918") then kq="顶级";
else if index(KHQDM,"P")^=0 then kq="高白";
else if index(KHQDM,"A")^=0 then kq="A";
else if index(KHQDM,"B")^=0 then kq="B";
else if index(KHQDM,"C")^=0 then kq="C";
else if index(KHQDM,"D")^=0 then kq="D";
else kq="其他";
format year $20.;
if OPEN_CARD_DT<="31dec2006"d then year="2006年及以前";
else if "1jan2007"d<=OPEN_CARD_DT<="31dec2007"d then year="2007年开户";
else if "1jan2008"d<=OPEN_CARD_DT<="31dec2008"d then year="2008年开户";
else if "1jan2009"d<=OPEN_CARD_DT<="31dec2009"d then year="2009年开户";
else if "1jan2010"d<=OPEN_CARD_DT<="31dec2010"d then year="2010年开户";
else if "1jan2011"d<=OPEN_CARD_DT<="31dec2011"d then year="2011年开户";
else if "1jan2012"d<=OPEN_CARD_DT<="31dec2012"d then year="2012年开户";
else if "1jan2013"d<=OPEN_CARD_DT<="31dec2013"d then year="2013年开户";
else if "1jan2014"d<=OPEN_CARD_DT<="31dec2014"d then year="2014
年开户";
else if "1jan2015"d<=OPEN_CARD_DT<="31dec2015"d then year="2015年开户";
else year="2015年开户";
format year2 $20.;
if datepart(audit_time)<"1jul2009"d or audit_time="" then year2="无纸化前";
else if "1jul2009"d<=datepart(audit_time)<="31dec2009"d then do;
if gdb_nbr^="" then year2="2009年新发卡";else year2="2009年其他";end;
else if "01jan2010"d<=datepart(audit_time)<="31dec2010"d then do;
if gdb_nbr^="" then year2="2010年新发卡";else year2="2010年其他";end;
else if "01jan2011"d<=datepart(audit_time)<="31dec2011"d then do;
if gdb_nbr^="" then year2="2011年新发卡";else year2="2011年其他";end;
else if "01jan2012"d<=datepart(audit_time)<="31dec2012"d then do;
if gdb_nbr^="" then year2="2012年新发卡";else year2="2012年其他";end;
else if "01jan2013"d<=datepart(audit_time)<="31dec2013"d then do;
if gdb_nbr^="" then year2="2013年新发卡";else year2="2013年其他";end;
else if "01jan2014"d<=datepart(audit_time)<="31dec2014"d then do;
if gdb_nbr^="" then year2="2014年新发卡";else year2="2014年其他";end;
else if gdb_nbr^="" then year2="2015年新发卡";else year2="2015年其他";
format CUST_INDUSTRY_CODE1 $60.;
if CUST_INDUSTRY_CODE in("1","23","26")then CUST_INDUSTRY_CODE1="政府机关、事业单位、公共事业" ;else
if CUST_INDUSTRY_CODE="2" then CUST_INDUSTRY_CODE1="科教文卫";else
if CUST_INDUSTRY_CODE="3" then CUST_INDUSTRY_CODE1="邮电通讯";else
if CUST_INDUSTRY_CODE="4" then CUST_INDUSTRY_CODE1="部队";else
if CUST_INDUSTRY_code in("5","35") then CUST_INDUSTRY_CODE1="通讯服务IT软件";else
if CUST_INDUSTRY_code in("6","31") then CUST_INDUSTRY_CODE1="国内商业贸易";else
if CUST_INDUSTRY_CODE="7" then CUST_INDUSTRY_CODE1="银行金融证券" ;else
if CUST_INDUSTRY_code in("8","30") then CUST_INDUSTRY_CODE1="房地产开发基础建设物管";else
if CUST_INDUSTRY_code="9" then CUST_INDUSTRY_CODE1="保险中介物管";else
if CUST_INDUSTRY_code in("10","24") then CUST_INDUSTRY_CODE1="旅游饭店宾馆娱乐";else
if CUST_INDUSTRY_CODE="11" then CUST_INDUSTRY_CODE1="自由职业个体店铺";else
if CUST_INDUSTRY_code="12" then CUST_INDUSTRY_CODE1="农林牧鱼";else
if CUST_INDUSTRY_code in("13","29","32") then CUST_INDUSTRY_CODE1="制造业";else
if CUST_INDUSTRY_code in("14","33")then CUST_INDUSTRY_CODE1="律师会计师专业顾问培训";else
if CUST_INDUSTRY_code in("15","38") then CUST_INDUSTRY_CODE1="其他";else
if CUST_INDUSTRY_code in("16","36") then CUST_INDUSTRY_CODE1="媒体出版广告";else
if CUST_INDUSTRY_code in("17","27")then CUST_INDUSTRY_CODE1="运输仓储物流邮政";else
if CUST_INDUSTRY_code="18" then CUST_INDUSTRY_CODE1="建筑业";else
if CUST_INDUSTRY_code="19" then CUST_INDUSTRY_CODE1="石油化工电力供水能源";else
if CUST_INDUSTRY_code in("20","34") then CUST_INDUSTRY_CODE1="进出口贸易";else
if CUST_INDUSTRY_code="21" then CUST_INDUSTRY_CODE1="金融保险
（一般职员）";else
if CUST_INDUSTRY_code="22" then CUST_INDUSTRY_CODE1="金融保险（销售）";else
if CUST_INDUSTRY_code="25" then CUST_INDUSTRY_CODE1="能源及通信服务";else
if CUST_INDUSTRY_code="28" then CUST_INDUSTRY_CODE1="批发零售百货";else
if CUST_INDUSTRY_code="37" then CUST_INDUSTRY_CODE1="医疗";else
CUST_INDUSTRY_CODE1="其他";
format danwei $30.;
if CUST_BUS_CODE=1 then danwei="机关、事业";
else if CUST_BUS_CODE=2 then danwei="私营、集体";
else if CUST_BUS_CODE=3 then danwei="中外合资合作";
else if CUST_BUS_CODE=4 then danwei="大型国有、股份制";
else if CUST_BUS_CODE=5 then danwei="私营、集体";
else if CUST_BUS_CODE=6 then danwei="个体";
else if CUST_BUS_CODE=7 then danwei="外商独资";
else danwei="其他";
format zhiwei $30.;
if CUST_POS_CODE=2 then zhiwei="1.厅局级及以上";
else if CUST_POS_CODE=3 then zhiwei="2.处级--机关事业类";
else if CUST_POS_CODE=4 then zhiwei="3.科级--机关事业类";
else if CUST_POS_CODE=5 then zhiwei="4.一般员工--机关事业类";
else if CUST_POS_CODE=6 then zhiwei="5.临时工--机关事业类";
else if CUST_POS_CODE=1 then zhiwei="6.企业负责人--其他类";
else if CUST_POS_CODE=11 then zhiwei="7.高级管理人员--其他类";
else if CUST_POS_CODE=21 then zhiwei="8.中级管理人员--其他类";
else if CUST_POS_CODE=7 then zhiwei="9.初级管理人员--其他类";
else if CUST_POS_CODE=31 then zhiwei="10.一般职员--其他类";
else if CUST_POS_CODE=8 then zhiwei="11.专业技术人员--其他类";
else if CUST_POS_CODE=14 then zhiwei="12.临时工--其他类";
else zhiwei="其他";
run;
/***剔除核销***/
data dq.acct_card_cust1505_fhx_cs;
set dq.acct_card_cust1505_cs_2;
if (LOCK_CODE not in ("Q") and LOCK_CODE_REASON_CD not in ("50")) or
(LOCK_CODE not in ("A") and LOCK_CODE_REASON_CD not in ("50"))
or (LOCK_CODE not in ("B") and LOCK_CODE_REASON_CD not in ("90"));
run;
ods html path="E:\管文英" body="地区分析底表-应收账款1505.xls" ;
title "开户时间";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class year crdacct_dlq;
table year ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "开卡时间";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class year2 crdacct_dlq;
table year2 ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "卡等级";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class CARD_LEVEL_CD crdacct_dlq;
table CARD_LEVEL_CD ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "卡等级1";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class card_grade1 crdacct_dlq;
table card_grade1 ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "行业";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class CUST_INDUSTRY_CODE1
crdacct_dlq;
table CUST_INDUSTRY_CODE1 ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "单位";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class danwei crdacct_dlq;
table danwei ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "职位";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class zhiwei crdacct_dlq;
table zhiwei ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "企业主、AB+";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class Cust_ABplus crdacct_dlq;
table Cust_ABplus ,crdacct_dlq*crdacct_rece_bal*sum;
run;
title "客群";
proc tabulate data=dq.acct_card_cust1505_fhx_cs_1 missing;
var crdacct_rece_bal;
class KHQDM crdacct_dlq;
table KHQDM ,crdacct_dlq*crdacct_rece_bal*sum;
run;
ods html close;
/********流量数据的提取,与月报底表相关*********/
/*data ll.liuliang1301;
set yb.Acct1301_b;
if liuliang_bj>0;
CRDACCT_NBR=substr(m_acct,5);
yymm=1301;
run;*/
data ll.liuliang1505;
set yb.Acct1505_b;
if liuliang_bj>0;
CRDACCT_NBR=substr(m_acct,5);
yymm=1505;
run;
data ll.Liuliang201501_02;
set ll.Liuliang1501 ll.liuliang1505;
run;