我这边结果已经弄出来了 我是分两个sql来处理的不知道还有没有更简单的方法 代码如下
proc sql;
create table d as
select d.SiteNumber,d.Subject,c.instanceId, c.FolderName, c.DataPageName, c.RecordPosition, c.CTNYN,
case FLRSNA
when '1' then compress(',不符合入组标准'||ifc(^missing(FLRSNASP),'('||FLRSNASP||')',''))
end as reason1,
case FLRSNB
when '1' then ',撤销知情'
end as reason2,
case FLRSNC
when '1' then compress(',不良事件'||ifc(^missing(FLRAETERM),'('||FLRAETERM||')',''))
end as reason3,
case FLRSND
when '1' then compress(',其他'||ifc(^missing(FAILSP),'('||FAILSP||')',''))
end as reason4
from c left join NEW.scr_fail d on c.subject=d.subject;
quit;
/*第二次处理*/
/*if ^missing(reason1) or ^missing(reason2) or ^missing(reason3) or ^missing(reason4) then */
/* FLRSN=substr(compress(reason1||reason2||reason3||reason4),2);*/
proc sql;
create table e as
select SiteNumber,Subject,instanceId, FolderName, DataPageName, RecordPosition, CTNYN,
case when(reason1 is not missing or reason2 is not missing or reason3 is not missing or reason4 is not missing )
then substr(compress(reason1||reason2||reason3||reason4),2)
end as FLRSN
from d;
quit;
|