/*proc sql;
create table temp1 as
select a.apply_num,substr(a.apply_num,1,4) as dep_num,b.dep_nam1,substr(a.wx_unit,1,4) as cons_team,
a.account_num,a.amend_type,substr(bao_date,1,8) as bzdate
from EW.DTEWR1 a,EN.DTEN90 b
where substr(a.apply_num,1,4)=b.dep_no
and "&d1"<=substr(a.bao_date,1,8)<="&d2"
and a.PROJECT_TYPE in ('F')
and a.amend_type='C3'
and a.wx_unit in ('QV00','1001','2001','1300','0500');
quit;*/
proc sql;
create table temp11 as
select distinct dep_nam1,count(apply_num) as apply_num,'A' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and &con
group by dep_nam1
;
quit;
/*proc sql;
create table temp2 as
select a.EC_ITEM,a.PLAN_DATE,b.dep_nam1
from EC.DTECC1 a,EN.DTEN90 b
where substr(a.WORKER_CODE,1,4)=b.dep_no
and substr(a.PLAN_DATE,1,8)<="&thismon"||"26"
and "&d1"<=substr(a.WORKER_CODE,1,8)<="&d2"
;
quit;
proc sql;
create table temp22 as
select distinct dep_nam1,count(EC_ITEM) as EC_ITEM_NUM,'B' as xm_type
from sub_sb.susb0082_2
where "&d1"<=substr(PLAN_DATE,1,8)<="&d2"
group by dep_nam1
;
quit;
*/
/*主题*/
/*
data dtecc2;
set ec.dtecc2;
keep ec_item PLAN_DATE;
if ec_flag='1' and PLAN_DATE>='20051226';
run;
data dtecc1;
set ec.dtecc1;
keep ec_item PLAN_DATE WORKER_CODE;
if PLAN_DATE>='20051226';
run;
proc sql;
create table temp2 as
select distinct substr(b.WORKER_CODE,1,2) as bm,count (a.ec_item) as EC_ITEM_NUM,a.PLAN_DATE
from dtecc2 a left join dtecc1 b
on a.ec_item=b.ec_item and a.PLAN_DATE=b.PLAN_DATE
group by substr(b.WORKER_CODE,1,2), a.PLAN_DATE
;quit;
%macro exam;
%if %sysfunc(exist(sub_sb.SUSB0000)) ne 0 %then
%do;
data sub_sb.susb0082_2;
set sub_sb.susb0082_2;
if PLAN_DATE>='20051226' then delete;
run;
%end;
proc sql;
create table temp22 as
select distinct dep_nam1,count(apply_num) as EC_ITEM_NUM,'B' as xm_type
from sub_sb.susb0082_2
where "&d1"<=substr(bzdate,1,8)<="&d2" and &con and PROJECT_TYPE ^= 'J'
group by dep_nam1
;
quit;
/*
proc sql;
create table temp22 as
select distinct bm,sum(EC_ITEM_NUM) as EC_ITEM_NUM,'B' as xm_type
from sub_sb.susb0082_2
where "&d1"<=substr(PLAN_DATE,1,8)<="&d2"
group by bm
;
quit;
proc sql;
create table temp22 as
select distinct a.bm,a.EC_ITEM_NUM,a.xm_type,b.dep_nam1
from temp22 a left join EN.DTEN90 b
on substr(b.dep_no,1,2)=a.bm
order by b.dep_nam1
;
quit;
data temp22(keep=dep_nam1 EC_ITEM_NUM xm_type);
set temp22;
run;
*/
/*proc sql;
create table temp3 as
select a.DEPT_APP_SEQ,a.DATE_ACCEPT,DEPT_CODE as dep_num,a.TREAT_MODE,b.dep_nam1
from EC.DTECD2 a,EN.DTEN90 b
where substr(a.DEPT_CODE,1,4)=b.dep_no and a.TREAT_MODE='3'
and substr(a.DATE_ACCEPT,1,8)<="&thismon"||"26"
and "&d1"<=substr(a.DATE_ACCEPT,1,8)<="&d2"
;
quit;*/
proc sql;
create table temp33 as
select distinct dep_nam1,count(apply_num) as DEPT_APP_SEQ_NUM,'C' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and compress(YCLLDH)^='' and &con and PROJECT_TYPE ^= 'J'
group by dep_nam1
;
quit;
/*
proc sql;
create table temp33 as
select distinct dep_nam1,count(DEPT_APP_SEQ) as DEPT_APP_SEQ_NUM,'C' as xm_type
from sub_sb.susb0082_3
where "&d1"<=substr(DATE_ACCEPT,1,8)<="&d2"
group by dep_nam1
;
quit;*/
/*
proc sql;
create table temp44 as
select distinct dep_nam1,count(apply_num) as gz_apply_num,'D' as xm_type
from sub_sb.susb0082_4
where "&d1"<=substr(bzdate,1,8)<="&d2"
group by dep_nam1
order by dep_nam1
;
quit;
proc sql;
create table temp45 as
select distinct dep_nam1,count(*) as gz_apply_num2,'D' as xm_type
from sub_sb.susb0082_5
where "&d1"<=substr(date_app,1,8)<="&d2"
group by dep_nam1
order by dep_nam1
;
quit;
data temp44;
merge temp44 temp45 ;
by dep_nam1;
gz_apply_num=sum(gz_apply_num,gz_apply_num2);
drop gz_apply_num2;
run;
*/
proc sql;
create table temp44 as
select distinct dep_nam1,count(apply_num) as gz_apply_num,'D' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and compress(EQ_FAULT_NUM)^='' and &con and PROJECT_TYPE ^= 'J'
group by dep_nam1
;
quit;
data temp;
merge temp11 temp22 temp33 temp44;
by dep_nam1;
/*if dep_nam1^='设备部';*/
run;
data temp;
set temp;
length code $2.;
if dep_nam1 = '炼铁厂' then do; code = 'AA'; end;
else if dep_nam1 = '炼钢厂' then do; code = 'AB'; end;
else if dep_nam1 = '条钢厂' then do; code = 'AC'; end;
else if dep_nam1 = '热轧厂' then do; code = 'AD'; end;
else if dep_nam1 = '冷轧厂' then do; code = 'AE'; end;
else if dep_nam1 = '电厂' then do; code = 'AF'; end;
else if dep_nam1 = '薄板厂' then do; code = 'AG'; end;
else if dep_nam1 = '厚板厂' then do; code = 'AH'; end;
else if dep_nam1 = '设备部' then do; code = 'BC'; end;
else if dep_nam1 = '运输部' then do; code = 'BD'; end;
else if dep_nam1 = '能源环保部' then do; code = 'BE'; end;
else if dep_nam1 = '钢管厂' then do; code = 'MA'; end;
else if dep_nam1 = '钢管事业部' then do; code = 'SG'; end;
else if dep_nam1 = '硅钢部' then do; code = 'MI'; end;
else if dep_nam1 = '检测公司' then do; code = 'QQ'; end;
else do; code = 'QY'; end;
RUN;
proc sort data = temp; by code; run;
data temp;
label wyt_ratio='比例';
set temp;
if apply_num=. then apply_num=0;
if EC_ITEM_NUM=. then EC_ITEM_NUM=0;
if DEPT_APP_SEQ_NUM=. then DEPT_APP_SEQ_NUM=0;
if gz_apply_num=. then gz_apply_num=0;
wyt_num=apply_num-EC_ITEM_NUM-DEPT_APP_SEQ_NUM-gz_apply_num;
if apply_num=0 then wyt_ratio=0;
if apply_num ^=0 then wyt_ratio=wyt_num/apply_num*100;
run;
data temp;
set temp;
if apply_num ^=0 ;
/*id=_n_;*/
run;
/*2009-10-09（何元良） 单独计算合计的比例字段*/
proc sql;
create table sum as
select "合计" as dep_nam1, "ZZ" as code, sum(apply_num) as apply_num, sum(EC_ITEM_NUM) as EC_ITEM_NUM, sum(EC_ITEM_NUM)/sum(apply_num)*100 as EC_ITEM_per,
sum(DEPT_APP_SEQ_NUM_NUM) as DEPT_APP_SEQ_NUM_NUM, sum(DEPT_APP_SEQ_NUM_NUM)/sum(apply_num)*100 as DEPT_APP_SEQ_per,
sum(gz_apply_num) as gz_apply_num, sum(gz_apply_num)/sum(apply_num)*100 as gz_apply_per
from temp
where dep_nam1^='化工公司' and dep_nam1^='检测公司';
quit;
/*-------------------------------------错误控制-------------------------------------------*/
%else %do;
data _null_;
file _webout ;
put '<html><body><font color=red><p><b>对不起，没有满足条件的数据。</b></font></p></body></html>';
run;
%end;
%macro bm2;
/*
proc sql;
create table temp11 as
select distinct dep_num as bm,count(apply_num) as apply_num,'A' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and substr(dep_num,1,&length_bm)="&bm"
group by bm
;
quit;
proc sql;
create table temp22 as
select distinct bm,sum(EC_ITEM_NUM) as EC_ITEM_NUM,'B' as xm_type
from sub_sb.susb0082_2
where "&d1"<=substr(PLAN_DATE,1,8)<="&d2" and substr(bm,1,&length_bm)="&bm"
group by bm
;
quit;
data temp22(keep=bm EC_ITEM_NUM xm_type);
set temp22;
run;
proc sql;
create table temp33 as
select distinct dep_no as bm,count(DEPT_APP_SEQ) as DEPT_APP_SEQ_NUM,'C' as xm_type
from sub_sb.susb0082_3
where "&d1"<=substr(DATE_ACCEPT,1,8)<="&d2" and substr(dep_no,1,&length_bm)="&bm"
group by bm
;
quit;
proc sql;
create table temp44 as
select distinct dep_num as bm,count(apply_num) as gz_apply_num,'D' as xm_type
from sub_sb.susb0082_4
where "&d1"<=substr(bzdate,1,8)<="&d2" and substr(dep_num,1,&length_bm)="&bm"
group by bm
order by bm
;
quit;
proc sql;
create table temp45 as
select distinct DEPT_CODE as bm,count(*) as gz_apply_num2,'D' as xm_type
from sub_sb.susb0082_5
where "&d1"<=substr(date_app,1,8)<="&d2" and substr(DEPT_CODE,1,&length_bm)="&bm"
group by bm
order by bm
;
quit;
data temp44;
merge temp44 temp45 ;
by bm;
gz_apply_num=sum(gz_apply_num,gz_apply_num2);
drop gz_apply_num2;
run;
*/
proc sql;
create table temp11 as
select distinct dep_num, dep_nam1,count(apply_num) as apply_num,'A' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and substr(dep_num,1,&length_bm)="&bm" and &con
group by dep_num
;
quit;
/*
proc sql;
select distinct dep_nam1 into :name
from sub_sb.susb0082_1
where substr(dep_num,1,&length_bm)="&bm";
quit;
*/
proc sql;
create table temp22 as
select distinct dep_num, dep_nam1,count(apply_num) as EC_ITEM_NUM,'B' as xm_type
from sub_sb.susb0082_2
where "&d1"<=substr(bzdate,1,8)<="&d2" and substr(dep_num,1,&length_bm)="&bm" and &con and PROJECT_TYPE ^= 'J'
group by dep_num
;
quit;
proc sql;
create table temp33 as
select distinct dep_num, dep_nam1,count(apply_num) as DEPT_APP_SEQ_NUM,'C' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and compress(YCLLDH)^='' and substr(dep_num,1,&length_bm)="&bm" and &con and PROJECT_TYPE ^= 'J'
group by dep_num
;
quit;
proc sql;
create table temp44 as
select distinct dep_num, dep_nam1,count(apply_num) as gz_apply_num,'D' as xm_type
from sub_sb.susb0082_1
where "&d1"<=substr(bzdate,1,8)<="&d2" and compress(EQ_FAULT_NUM)^='' and substr(dep_num,1,&length_bm)="&bm" and &con and PROJECT_TYPE ^= 'J'
group by dep_num
;
quit;
data temp;
merge temp11 temp22 temp33 temp44;
by dep_num;
/*if dep_nam1^='设备部';*/
run;
data temp;
label wyt_ratio='比例';
set temp;
if apply_num=. then apply_num=0;
if EC_ITEM_NUM=. then EC_ITEM_NUM=0;
if DEPT_APP_SEQ_NUM=. then DEPT_APP_SEQ_NUM=0;
if gz_apply_num=. then gz_apply_num=0;
wyt_num=apply_num-EC_ITEM_NUM-DEPT_APP_SEQ_NUM-gz_apply_num;
if apply_num=0 then wyt_ratio=0;
if apply_num ^=0 then wyt_ratio=wyt_num/apply_num*100;
run;
data temp;
label wyt_ratio='比例';
set temp;
if apply_num=. then apply_num=0;
if EC_ITEM_NUM=. then EC_ITEM_NUM=0;
if DEPT_APP_SEQ_NUM=. then DEPT_APP_SEQ_NUM=0;
if gz_apply_num=. then gz_apply_num=0;
wyt_num=apply_num-EC_ITEM_NUM-DEPT_APP_SEQ_NUM-gz_apply_num;
if apply_num=0 then wyt_ratio=0;
if apply_num ^=0 then wyt_ratio=wyt_num/apply_num*100;
run;
data temp;
set temp;
if apply_num ^=0 ;
/*id=_n_;*/
run;
/*2009-10-09（何元良） 单独计算合计的比例字段*/
proc sql;
create table sum as
select "合计" as dep_num, sum(apply_num) as apply_num, sum(EC_ITEM_NUM) as EC_ITEM_NUM, sum(EC_ITEM_NUM)/sum(apply_num)*100 as EC_ITEM_per,
sum(DEPT_APP_SEQ_NUM_NUM) as DEPT_APP_SEQ_NUM_NUM, sum(DEPT_APP_SEQ_NUM_NUM)/sum(apply_num)*100 as DEPT_APP_SEQ_per,
sum(gz_apply_num) as gz_apply_num, sum(gz_apply_num)/sum(apply_num)*100 as gz_apply_per
from temp
quit;
/*-------------------------------------错误控制-------------------------------------------*/
%else %do;
data _null_;
file _webout ;
put '<html><body><font color=red><p><b>对不起，没有满足条件的数据。</b></font></p></body></html>';
run;
%end;
data _null_;
file _webout;
put '<tr><td align="center" width="100%" height="16" bgcolor="#00FFFF">';
put "<a href=&download_path>确认下载</a>";
put '</td>';
put '</tr>';
put '<tr>';
put '<td width="100%" height="16" bgcolor="#FFFCF0">';
put '</td>';
put '</tr>';
run;
data _null_;
file _webout;
put '</table></body></html>';
run;
ods html close;
%end;
%else %do;
data _null_;
file _webout ;
put '<html><body><font color=red><p><b>对不起，没有满足条件的数据。</b></font></p></body></html>';
run;
%end;