h******s
发帖数: 3420 | 1 有两个文件
file1 按 id 和 date 列出病人的event
Id. Event_date
001. 02/11/1977
001. 03/08/1977
001. 07/11/1978
001. 02/05/1979
002. 04/06/1977
002. 03/09/1978
002. 04/11/1978
..........
File2 按id和date 列出病人的admission date
Id. Admission_date
001. 02/11/1977
001. 07/11/1978
002. 04/06/1977
......
请问怎样从file2 中找到file1 event 对应的admission date?
希望输出为：
Id. Event_date. Admission_date
001. 02/11/1977. 02/11/1977
001. 03/08/1977. 02/11/1977
001. 07/11/1978. 07/11/1978
001. 02/05/1979. 07/11/1978
002. 04/06/1977. 04/06/1977
002. 03/09/1978. 04/06/1977
002. 04/11/1978. 04/06/1977
万分感谢！ |
h********o
发帖数: 103 | 2 Try this:
====================================
PROC SQL;
CREATE TABLE FINAL AS
SELECT A.ID,
A.EVENT_DATE,
B.ADMISSION_DATE
FROM FILE1 AS A LEFT JOIN FILE2 AS B
ON A.ID = B.ID AND A.EVENT_DATE >= B.ADMISSION_DATE
ORDER BY ID, EVENT_DATE, ADMISSION_DATE DESC;
QUIT;
PROC SORT DATA = FINAL NODUPKEY;
BY ID EVENT_DATE;
RUN; |
h******s
发帖数: 3420 | 3 谢谢
我后来想出来也是这个思路，不过你的code 好多了
两包子
【在 h********o 的大作中提到】
: Try this:
: ====================================
: PROC SQL;
: CREATE TABLE FINAL AS
: SELECT A.ID,
: A.EVENT_DATE,
: B.ADMISSION_DATE
: FROM FILE1 AS A LEFT JOIN FILE2 AS B
: ON A.ID = B.ID AND A.EVENT_DATE >= B.ADMISSION_DATE
: ORDER BY ID, EVENT_DATE, ADMISSION_DATE DESC;
|
s******r
发帖数: 1524 | 4 You can do with sql in one step, why use sort.
PROC SQL;
CREATE TABLE FINAL AS
SELECT A.ID,
A.EVENT_DATE format=mmddyy8.,
max(B.ADMISSION_DATE) format=mmddyy8. as max_admiss
FROM FILE1 AS A LEFT JOIN FILE2 AS B
ON A.ID = B.ID AND A.EVENT_DATE >= B.ADMISSION_DATE
group by a.id,a.event_date
ORDER BY ID, EVENT_DATE;
QUIT;
【在 h********o 的大作中提到】
: Try this:
: ====================================
: PROC SQL;
: CREATE TABLE FINAL AS
: SELECT A.ID,
: A.EVENT_DATE,
: B.ADMISSION_DATE
: FROM FILE1 AS A LEFT JOIN FILE2 AS B
: ON A.ID = B.ID AND A.EVENT_DATE >= B.ADMISSION_DATE
: ORDER BY ID, EVENT_DATE, ADMISSION_DATE DESC;
|
h********o
发帖数: 103 | 5 Run your code to see if you can get what he want.
Also you need to change the name of "max_admiss " back to "admission_date"
finally. |
h********o
发帖数: 103 | 6 PROC SQL;
CREATE TABLE FINAL AS
SELECT A.ID,
A.EVENT_DATE FORMAT = MMDDYY10.,
CASE
WHEN MAX(B.ADMISSION_DATE) THEN MAX(B.ADMISSION_DATE)
END AS ADMISSION_DATE FORMAT = MMDDYY10.
FROM FILE1 AS A LEFT JOIN FILE2 AS B
ON A.ID = B.ID AND A.EVENT_DATE >= B.ADMISSION_DATE
GROUP BY A.ID, A.EVENT_DATE
ORDER BY ID, EVENT_DATE;
QUIT; |
s******r
发帖数: 1524 | 7 it works. variable name is just a name
【在 h********o 的大作中提到】
: Run your code to see if you can get what he want.
: Also you need to change the name of "max_admiss " back to "admission_date"
: finally.
|
z******1
发帖数: 9 | 8 是不是用merge就行了？
data d3;
merge d1 d2;
by id;
run; |
p********r
发帖数: 1465 | |
n**m
发帖数: 156 | 10 这一段我知道做了啥，不过不太明白这些code，能不能解释一下。谢谢
CASE
WHEN MAX(B.ADMISSION_DATE) THEN MAX(B.ADMISSION_DATE)
END AS ADMISSION_DATE FORMAT = MMDDYY10.
【在 h********o 的大作中提到】
: PROC SQL;
: CREATE TABLE FINAL AS
: SELECT A.ID,
: A.EVENT_DATE FORMAT = MMDDYY10.,
: CASE
: WHEN MAX(B.ADMISSION_DATE) THEN MAX(B.ADMISSION_DATE)
: END AS ADMISSION_DATE FORMAT = MMDDYY10.
: FROM FILE1 AS A LEFT JOIN FILE2 AS B
: ON A.ID = B.ID AND A.EVENT_DATE >= B.ADMISSION_DATE
: GROUP BY A.ID, A.EVENT_DATE
|
f*****k
发帖数: 110 | 11 I guess the effect of the CASE statement is equivalent to huaifans' code --
max(B.ADMISSION_DATE) format=mmddyy8. as max_admiss. But I am not sure why
using CASE statement here. In addition, why use LEFT JOIN? I tried JOIN only
and it worked.
【在 n**m 的大作中提到】
: 这一段我知道做了啥，不过不太明白这些code，能不能解释一下。谢谢
: CASE
: WHEN MAX(B.ADMISSION_DATE) THEN MAX(B.ADMISSION_DATE)
: END AS ADMISSION_DATE FORMAT = MMDDYY10.
|
l******n
发帖数: 9344 | 12 有event才有ad date吧
【在 f*****k 的大作中提到】
: I guess the effect of the CASE statement is equivalent to huaifans' code --
: max(B.ADMISSION_DATE) format=mmddyy8. as max_admiss. But I am not sure why
: using CASE statement here. In addition, why use LEFT JOIN? I tried JOIN only
: and it worked.
|
