各位老师好:
请问如何用sql左合并2个数据集:
card1中每个id有1条观测,
card2中每个id有多条观测(只保留首次(var58=日期)不为缺失值的检测结果(var57=数值)),
如何将card2中首次不为缺失值的检测检测结果通过id匹配到card1中?
上述如何用sql实现?
谢谢。
下面这段sql运行后提示错误:
RROR 22-322: 语法错误,期望下列之一: 名称, 带引号的字符串, 数值常数, 日期时间常数, 缺失值, (,
+, -, INPUT, NOT, PUT, ^, ~.
ERROR 201-322: 该选项不可识别,将被忽略。
ERROR 22-322: 语法错误,期望下列之一: 名称, 带引号的字符串, 数值常数, 日期时间常数, 缺失值, (,
), +, ',', -, INPUT, NOT, PUT, ^, ~.
ERROR 201-322: 该选项不可识别,将被忽略。
ERROR 22-322: 语法错误,期望下列之一: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?,
AND, BETWEEN, CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT, NOTIN, OR, ^,
^=, |, ||, ~, ~=.
ERROR 76-322: 语法错误,语句将被忽略。
ERROR: 解析 WHERE 子句时出现语法错误。
ERROR: 对于“WHERE”选项值无效。
proc sql;
create table card_ as
select a.*,
b.var1,
b.var53,
b.var54,
b.var57,
b.var58,
b.var59,
b.var60,
b.var61
from card1(where=(areatype not in ("港澳台" "外籍") and
flagoval eq "已终审卡" and
typeocase in ("实验室诊断病例" "临床诊断病例") and
dateotype between '01jul1985'd and '30jun2014'd and
int(addcode/1000000) eq 44)) as a
left join card2(where=(var57 is not missing and first.input(compress(b.var1,"‘’"),$40.) and first.var58)) as b
on input(compress(a.card_id,"‘’"),$40.) = input(compress(b.var1,"‘’"),$40.);
quit;