|
|
楼主
楼主 |
发表于 2011-4-20 15:47:02
|
只看该作者
请教一个问题 急
问题1：客户需要下载第二个字段中 点检异常部门的数据，可是点击超链接后，连同第一列的 源于点检结果部分的也数据也下载进去了。
问题2：在下载数据时，添加一个表EC.dtecd1表中的APP_deta字段 主键是 DEPT_APP_SEQ ; 我试着添加过，但这个字段只有字段名，里面的数据添加不进去；
下面是表代码；
%inc '/dw/prog/mart_sb/rpdprog/sb_stp_initial.sas' ;
%macro bm1;
/*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;
%mend exam;
%exam;
proc append data=temp2 base=sub_sb.susb0082_2 force;
run;
*/
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;
DEPT_APP_SEQ_NUM_NUM = DEPT_APP_SEQ_NUM - EC_ITEM_NUM;
EC_ITEM_per = EC_ITEM_NUM / apply_num *100;
DEPT_APP_SEQ_per = DEPT_APP_SEQ_NUM_NUM / apply_num *100;
gz_apply_per = gz_apply_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;
data temp;
set temp sum;
run;
data temp;
set temp;
length EC_ITEM_NUM_n $250;
length DEPT_APP_SEQ_NUM_NUM_n $250;
length gz_apply_num_n $250;
EC_ITEM_NUM_n='<a href="' || "&_URL" || "?_service=&_service" ||
'&_debug=0' ||
'&_program=' || "SBIP://Foundation/dw/mart_sb/jxgl/rpsb0082p" ||
'&d1=' || compress(&d1) ||
'&d2=' || compress(&d2) ||
'&C1=' || compress("&C1") ||
'&C3=' || compress("&C3") ||
'&C5=' || compress("&C5") ||
'&C6=' || compress("&C6") ||
'&bm=' || compress(code) ||
'&type=' || compress(1) ||
'&flag=' || compress(2) ||
'"target=_blank>' ||
htmlencode(EC_ITEM_NUM) ||'</a>';
/*需要在下面改动*/
DEPT_APP_SEQ_NUM_NUM_n='<a href="' || "&_URL" || "?_service=&_service" ||
'&_debug=0' ||
'&_program=' || "SBIP://Foundation/dw/mart_sb/jxgl/rpsb0082p" ||
'&d1=' || compress(&d1) ||
'&d2=' || compress(&d2) ||
'&C1=' || compress("&C1") ||
'&C3=' || compress("&C3") ||
'&C5=' || compress("&C5") ||
'&C6=' || compress("&C6") ||
'&bm=' || compress(code) ||
'&type=' || compress(2) ||
'&flag=' || compress(2) ||
'"target=_blank>' ||
htmlencode(DEPT_APP_SEQ_NUM_NUM) ||'</a>';
gz_apply_num_n='<a href="' || "&_URL" || "?_service=&_service" ||
'&_debug=0' ||
'&_program=' || "SBIP://Foundation/dw/mart_sb/jxgl/rpsb0082p" ||
'&d1=' || compress(&d1) ||
'&d2=' || compress(&d2) ||
'&C1=' || compress("&C1") ||
'&C3=' || compress("&C3") ||
'&C5=' || compress("&C5") ||
'&C6=' || compress("&C6") ||
'&bm=' || compress(code) ||
'&type=' || compress(3) ||
'&flag=' || compress(2) ||
'"target=_blank>' ||
htmlencode(gz_apply_num) ||'</a>';
run;
proc sql;
select count(*) into :n
from temp;
quit;
%if &n ne 0 %then %do;
data _null_;
file _webout;
put '<html><head><script src="/select.js"></script><SCRIPT src="/showhide.js"></SCRIPT><meta http-equiv="Content-Type" content="text/html; charset=gb2312"></head></html>';
run;
options nodate nonumber pageno=1 ps=9999 ls=250;
ods html file=_webout style=sasweb ;
proc report data=temp style=[just=center];
column dep_nam1 apply_num EC_ITEM_NUM_n EC_ITEM_per DEPT_APP_SEQ_NUM_NUM_n DEPT_APP_SEQ_per gz_apply_num_n gz_apply_per;
define dep_nam1 / order '单元' order=data;
define apply_num / display '状态项目数' ;
define EC_ITEM_NUM_n / display '源于点检结果' ;
define EC_ITEM_per / display format=8.2 '比例(%)' ;
define DEPT_APP_SEQ_NUM_NUM_n / display '源于设备异常信息' ;
define DEPT_APP_SEQ_per / display format=8.2 '比例(%)' ;
define gz_apply_num_n / display '源于设备故障' ;
define gz_apply_per / display format=8.2 '比例(%)' ;
where dep_nam1^='化工公司' /*and dep_nam1^='设备部'*/ and dep_nam1^='检测公司';
title1 "<font face='华文行楷' color=blue size=4><b>宝 钢 分 公 司</b></font><br>";
title2 "<font face='楷体_GB2312' color=red size=5><b> &title2 </b></font>";
title3 "<font face='黑体' color=blue><b>&title3</b></font> ";
run;
proc report data=temp style=[just=center];
column dep_nam1 apply_num EC_ITEM_NUM_n EC_ITEM_per DEPT_APP_SEQ_NUM_NUM_n DEPT_APP_SEQ_per gz_apply_num_n gz_apply_per;
define dep_nam1 / order '单元' order=data;
define apply_num / display '状态项目数' ;
define EC_ITEM_NUM_n / display '源于点检结果' ;
define EC_ITEM_per / display format=8.2 '比例(%)' ;
define DEPT_APP_SEQ_NUM_NUM_n / display '源于设备异常信息' ;
define DEPT_APP_SEQ_per / display format=8.2 '比例(%)' ;
define gz_apply_num_n / display '源于设备故障' ;
define gz_apply_per / display format=8.2 '比例(%)' ;
where dep_nam1='化工公司' or dep_nam1='检测公司';
title1 ;
title2 ;
title3 ;
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;
proc datasets lib=work memtype=data kill nolist;quit;
%mend bm1;
%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;
DEPT_APP_SEQ_NUM_NUM = DEPT_APP_SEQ_NUM - EC_ITEM_NUM;
EC_ITEM_per = EC_ITEM_NUM / apply_num *100;
DEPT_APP_SEQ_per = DEPT_APP_SEQ_NUM_NUM / apply_num *100;
gz_apply_per = gz_apply_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;
data temp;
set temp sum;
run;
data temp;
set temp;
length EC_ITEM_NUM_n $250;
length DEPT_APP_SEQ_NUM_NUM_n $250;
length gz_apply_num_n $250;
EC_ITEM_NUM_n='<a href="' || "&_URL" || "?_service=&_service" ||
'&_debug=0' ||
'&_program=' || "SBIP://Foundation/dw/mart_sb/jxgl/rpsb0082p" ||
'&d1=' || compress(&d1) ||
'&d2=' || compress(&d2) ||
'&C1=' || compress("&C1") ||
'&C3=' || compress("&C3") ||
'&C5=' || compress("&C5") ||
'&C6=' || compress("&C6") ||
'&bm=' || compress(dep_num) ||
'&type=' || compress(1) ||
'&flag=' || compress(2) ||
'"target=_blank>' ||
htmlencode(EC_ITEM_NUM) ||'</a>';
DEPT_APP_SEQ_NUM_NUM_n='<a href="' || "&_URL" || "?_service=&_service" ||
'&_debug=0' ||
'&_program=' || "SBIP://Foundation/dw/mart_sb/jxgl/rpsb0082p" ||
'&d1=' || compress(&d1) ||
'&d2=' || compress(&d2) ||
'&C1=' || compress("&C1") ||
'&C3=' || compress("&C3") ||
'&C5=' || compress("&C5") ||
'&C6=' || compress("&C6") ||
'&bm=' || compress(dep_num) ||
'&type=' || compress(2) ||
'&flag=' || compress(2) ||
'"target=_blank>' ||
htmlencode(DEPT_APP_SEQ_NUM_NUM) ||'</a>';
gz_apply_num_n='<a href="' || "&_URL" || "?_service=&_service" ||
'&_debug=0' ||
'&_program=' || "SBIP://Foundation/dw/mart_sb/jxgl/rpsb0082p" ||
'&d1=' || compress(&d1) ||
'&d2=' || compress(&d2) ||
'&C1=' || compress("&C1") ||
'&C3=' || compress("&C3") ||
'&C5=' || compress("&C5") ||
'&C6=' || compress("&C6") ||
'&bm=' || compress(dep_num) ||
'&type=' || compress(3) ||
'&flag=' || compress(2) ||
'"target=_blank>' ||
htmlencode(gz_apply_num) ||'</a>';
run;
proc sql;
select count(*) into :n
from temp;
quit;
%if &n ne 0 %then %do;
data _null_;
file _webout;
put '<html><head><script src="/select.js"></script><SCRIPT src="/showhide.js"></SCRIPT><meta http-equiv="Content-Type" content="text/html; charset=gb2312"></head></html>';
run;
options nodate nonumber pageno=1 ps=9999 ls=250;
ods html file=_webout style=sasweb ;
proc report data=temp style=[just=center];
column dep_num apply_num EC_ITEM_NUM_n EC_ITEM_per DEPT_APP_SEQ_NUM_NUM_n DEPT_APP_SEQ_per gz_apply_num_n gz_apply_per;
define dep_num / order '单元' order=data;
define apply_num / display '状态项目数' ;
define EC_ITEM_NUM_n / display '源于点检结果' ;
define EC_ITEM_per / display format=8.2 '比例(%)' ;
define DEPT_APP_SEQ_NUM_NUM_n / display '源于设备异常信息' ;
define DEPT_APP_SEQ_per / display format=8.2 '比例(%)' ;
define gz_apply_num_n / display '源于设备故障' ;
define gz_apply_per / display format=8.2 '比例(%)' ;
title1 "<font face='华文行楷' color=blue size=4><b>宝 钢 分 公 司</b></font><br>";
title2 "<font face='楷体_GB2312' color=red size=5><b> &title2 </b></font>";
title3 "<font face='黑体' color=blue><b>&title3</b></font> ";
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;
proc datasets lib=work memtype=data kill nolist;quit;
%mend bm2;
%macro dl;
%let d1=%cmpres(%superq(d1));
%if "&type" EQ "1" %then %do;
%let con2 = and PROJECT_TYPE ^= 'J';
%let dataset = susb0082_2;
%end;
%else %if "&type" EQ "2" %then %do;
%let con2 = and PROJECT_TYPE ^= 'J' and compress(YCLLDH)^='';
%let dataset = susb0082_1;
%end;
%else %then %do;
%let con2 = and PROJECT_TYPE ^= 'J' and compress(EQ_FAULT_NUM)^='';
%let dataset = susb0082_1;
%end;
%put &con2;
%put &con;
%if "&bm" eq "ZZ" or "&bm" eq "合计" %then %do;
%let con3 = 1;
%end;
%else %then %do;
%let con3 = substr(dep_num,1,length("&bm")) = "&bm";
%end;
%put &con3;
data temp;
set sub_sb.&dataset;
if "&d1"<=substr(bzdate,1,8)<="&d2" and &con3 and &con &con2;
keep APPLY_NUM STANDARD_NO EXAM_TYPE AMEND_TYPE DJ_MAN YCLLDH EQ_FAULT_NUM F_DATE START_DATE APPLY_DATE PROJECT_NAME M_NUMBER PLAN_NUM;
label APPLY_NUM = "委托单编号"
STANDARD_NO = "标准项目编号"
EXAM_TYPE = "施工类别"
AMEND_TYPE = "维修工事分类"
DJ_MAN = "点检岗位代号"
YCLLDH = "异常信息联络单号"
EQ_FAULT_NUM = "设备故障台帐编号"
F_DATE = "完工日期"
START_DATE = "开工日期";
run;
data temp1;
set EC.DTECD2;
keep DEPT_CODE WORKER_CODE_TREAT DATE_ACCEPT EC_ITEM_ACT EC_ITEM_SEQ_ACT TREAT_DETAIL DATE_END DEPT_APP_SEQ TREAT_DETAIL;
label DEPT_CODE = "受理部门"
WORKER_CODE_TREAT = "处理人"
DATE_ACCEPT = "受理日期"
EC_ITEM_ACT = "实际点检项目"
EC_ITEM_SEQ_ACT = "项次号"
TREAT_DETAIL = "处理意见"
DATE_END = "完成日期"
TREAT_DETAIL = "状况说明";
run;
/* */
proc sql;
create table rpsb0082_download as
select a.*, b.*
from temp a left join temp1 b
on a.YCLLDH = b.DEPT_APP_SEQ;
quit;
proc sql;
select count(*) into :nt
from rpsb0082_download;
quit;
%if &nt ne 0 %then %do;
data _null_;
file _webout;
put '<html><head><script src="/select.js"></script><SCRIPT src="/showhide.js"></SCRIPT><meta http-equiv="Content-Type" content="text/html; charset=gb2312"></head>';
put '<body><table width=100%><tr><td align=center><font face="华文行楷" color=blue size=5><b>宝 钢 分 公 司</b></font></td></tr></table>';
put "<table width=100%><tr><td align=center> <font face='楷体_GB2312' color=red size=5> <b> &title2 </b> </font></td></tr>";
put '<tr><td align=center><font face="黑体" color=blue>';
put "&title3";
put '</font> </td></tr>';
run;
%let path=%sysfunc(compress(/dw/download/mart_sb/rpsb0082_download_&bm..dbf));
%put &path;
proc EXPORT DATA=rpsb0082_download
OUTFILE= "&path"
DBMS=DBF REPLACE;
run;
%let download_path = /frame/dl/mart_sb/rpsb0082_download_&bm..dbf;
%put &download_path;
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;
%mend dl;
%macro rpsb0082;
%let d1=%cmpres(%superq(d1));
%let d2=%cmpres(%superq(d2));
%let bm=%cmpres(%superq(bm));
%let length_bm=%length(&bm) ;
%put &length_bm ;
%let C1=%cmpres(%superq(C1));
%let C3=%cmpres(%superq(C3));
%let C5=%cmpres(%superq(C5));
%let C6=%cmpres(%superq(C6));
%put &C1 ;
%let flag=%cmpres(%superq(flag));
%put &flag ;
%if "&d1" ne "" and "&d2" ne "" %then %do;
%let title2=&d1 至 &d2 常规检修状态项目源头实绩数据统计分析;
%end;
%else %if "&d1"="" %then %do;
%let title2=&d2 常规检修状态项目源头实绩数据统计分析;
%end;
%else %if "&d2"="" %then %do;
%let title2=&d1 常规检修状态项目源头实绩数据统计分析;
%end;
%if "&C1" EQ "C1" %then %do;
%let con = 1;
%let title3=工事分类: 全部;
%end;
%if "&C1" NE "C1" %then %do;
%let con = (AMEND_TYPE in ("&C3", "&C5", "&C6"));
%let title3=工事分类: &C3 &C5 &C6;
%end;
%if &flag = 1 %then %do;
%if &length_bm=0 %then %bm1;
%else %if &length_bm=2 %then %bm2;
%else %then %do;
data _null_;
file _webout;
put '<font color=red>部门代码输入不正确！';
run;
%end;
%end;
%else %then %do;
%dl;
%end;
%mend rpsb0082;
%rpsb0082; |
|
