我很感谢对这项任务的一些帮助,我发现这有点挑战性我有两个表: 1-表1:包含根据ICD代码系统编码的患者和他们的诊断(该表实际上有几千行)
Patient Diagnosis
JOHN A1
ALEX A12
ZAK K12
RICHARD A25
SAM G3
MANNY H22-表2:基本上包含多个ICD诊断组,为了简单起见,假设我们有三列A、B、C,每一列都有代表多个ICD诊断代码的行,这里是一个简化:
Group A Group B Group C
A12 A21 C13
A25 A33 J12
H12 G1 A1
K12 K77 L5
G1 J12 A12
D3 A25 G3我想要做的是创建第三个表,它本质上是表1加上A、B和C组的另外三列。然后,代码将遍历每一行,看看表1中的ICD诊断是否符合A、B和C组中的任何诊断,如果是,它将用1填充相应的单元格,否则为0。下面是最后一张表来解释一下:
Diagnosis Group A Group B Patient Group C
A1 0 0 JOHN 1
A12 1 0 ALEX 1
K12 1 0 ZAK 0
A25 1 1 RICHARD 0
G3 0 0 SAM 1
H2 0 0 MANNY 0有人能建议一下如何做到这一点吗?现实中的表格当然要大得多
发布于 2015-06-09 21:48:11
您可以使用PROC SQL语句来完成此操作。我做了这样的事情。
DATA TABLE1;
INPUT PATIENT $ DIAGNOSIS $;
CARDS;
JOHN A1
ALEX A12
ZAK K12
RICHARD A25
SAM G3
MANNY H2
;
RUN;
DATA TABLE2;
INPUT GROUPA $ GROUPB $ GROUPC $;
CARDS;
A12 A21 C13
A25 A33 J12
H12 G1 A1
K12 K77 L5
G1 J12 A12
D3 A25 G3
;
RUN;
PROC SQL;
CREATE TABLE RESULTSET AS
SELECT
A.DIAGNOSIS,
MAX(CASE WHEN B.GROUPA IS NULL THEN 0 ELSE 1 END) AS GROUPA,
MAX(CASE WHEN C.GROUPB IS NULL THEN 0 ELSE 1 END) AS GROUPB,
A.PATIENT,
MAX(CASE WHEN D.GROUPC IS NULL THEN 0 ELSE 1 END) AS GROUPC
FROM TABLE1 A
LEFT JOIN TABLE2 B
ON A.DIAGNOSIS = B.GROUPA
LEFT JOIN TABLE2 C
ON A.DIAGNOSIS = C.GROUPB
LEFT JOIN TABLE2 D
ON A.DIAGNOSIS = D.GROUPC
GROUP BY
A.DIAGNOSIS,
A.PATIENT;
QUIT;发布于 2015-06-09 23:28:38
如果求助于数据步骤,这里有一种方法(假设您RAM足够大,可以容纳整个ICD代码字典),可能比Proc SQL更有效。
data have;
input (Patient Diagnosis) (:$8.);
cards;
JOHN A1
ALEX A12
ZAK K12
RICHARD A25
SAM G3
MANNY H2
;
data codes;
input (GroupA GroupB GroupC) (:$8.);
cards;
A12 A21 C13
A25 A33 J12
H12 G1 A1
K12 K77 L5
G1 J12 A12
D3 A25 G3
;
data want;
if _n_=1 then
do;
declare hash h();
h.definekey('group','value');
h.definedone();
do until (last);
set codes end=last;
array grp _character_;
do over grp;
group=vname(grp);
value=grp;
rc=h.replace();
end;
end;
end;
set have;
grpa=not h.check(key:'GroupA', key:diagnosis);
grpb=not h.check(key:'GroupB', key:diagnosis);
grpc=not h.check(key:'GroupC', key:diagnosis);
drop group: value rc;
run;发布于 2015-06-10 04:24:35
数组的效率和速度都低于哈希表,但似乎不受内存的限制。
data want;
set table1;
if _n_=1 then do;
do i=1 by 1 until(last);
set table2 end=last;
array dig[10000] $ _temporary_;
array grp groupa--groupc;
do j=1 to 3;
n+1;
dig(n)=grp(j);
end;
end;
end;
grpA=0;grpB=0;grpC=0;
do i=1 to n;
if DIAGNOSIS=dig(i) then do;
if mod(i,3)=1 then grpA=1;
else if mod(i,3)=2 then grpB=1;
else grpC=1;
end;
end;
drop i j n groupA--GroupC;
run;https://stackoverflow.com/questions/30733482
复制相似问题