p******s
发帖数: 229 | 1 There are 2 datasets, A and B. There is a variable called drug_id in each
dataset. How can we join thess 2 datasets and exclude drug_ids that exist
in B?
I know how to do it in DATA step (merge A (in=a) B (in=b); by drug_id; if a
and not b; run;), but have no idea how to do in PROC SQL.
Thank you. |
h******e
发帖数: 1791 | 2 it is a left join.
select A.drug_id, , , ,
from A left join B on A.drug_id = B.drug_id |
p******s
发帖数: 229 | 3 That is what I thought at first. but this way will keep drug_ids which are
in both A and B, besides those in A. Our purpose is to not include any drug_
ids that exist in B. |
p******s
发帖数: 229 | 4 I got it by searching google.
the code is like this
select *
from A LEFT JOIN B
on A.drug_id = B.drug_id
where B.drug_id IS NULL
Thanks to hehehehe for the reply! |
h******e
发帖数: 1791 | 5 学到一招，谢谢。
【在 p******s 的大作中提到】
: I got it by searching google.
: the code is like this
: select *
: from A LEFT JOIN B
: on A.drug_id = B.drug_id
: where B.drug_id IS NULL
: Thanks to hehehehe for the reply!
|
d******9
发帖数: 404 | 6 I guess all the values of variables from data B will be missing. If so, we
can also do it by SQL in 2 steps.
Proc SQL;
create table C as
select *
from A
where A.ID not in
(select ID from B);
quit;
proc sql;
create table Z as
select *
from C left join B
on C.ID=B.ID;
quit; |
j******o
发帖数: 127 | 7 这招不错，学习了，谢谢。
【在 p******s 的大作中提到】
: I got it by searching google.
: the code is like this
: select *
: from A LEFT JOIN B
: on A.drug_id = B.drug_id
: where B.drug_id IS NULL
: Thanks to hehehehe for the reply!
|
n**m
发帖数: 156 | |
p******s
发帖数: 229 | |
m*****y
发帖数: 229 | 10 I think the idea of hehehe is correct, but not necessary left join I think.
DATA A;
INPUT drug_id x;
datalines;
1 2
;
data B;
input drug_id y;
datalines;
1 4
;
proc sql;
select A.drug_id, x, y
from A,B
where A.drug_id=B.drug_id;
quit;
result:
drug_id x y
1 2 4
drug_
【在 p******s 的大作中提到】
: That is what I thought at first. but this way will keep drug_ids which are
: in both A and B, besides those in A. Our purpose is to not include any drug_
: ids that exist in B.
|
