Hi - I'm doing a conditional all-to-all merge in SQL with the code below. It works; but seems silly to have to create the table twice. I'm looking for a way to select randomly from the rows of the joined table before writing it out. proc sql;
create table inftrans as select
a.patientid as patientid, a.hospsize as hospsize, b.patientid as inf_source, 1 as newinf,
a.hospid as whereinf, a.daysin as dayinf
from work.patients (where=(infected=0)) as a, work.patients (where=(infected=1)) as b
where a.hospid=b.hospid & a.patientid^=b.patientid;
create table infrtrans as select * from inftrans
where ranbin(0,1,10/(hospsize**2))=1;
quit; When I try adding the condition to the first where statement like this: proc sql;
create table inftrans as select
a.patientid as patientid, a.hospsize as hospsize, b.patientid as inf_source, 1 as newinf,
a.hospid as whereinf, a.daysin as dayinf
from work.patients (where=(infected=0)) as a, work.patients (where=(infected=1)) as b
where a.hospid=b.hospid & a.patientid^=b.patientid & ranbin(0,1,10/(hospsize**2))=1;
quit; inftrans will apply the ranbin() condition to a single row from the input dataset & thus be true multiple times. So the question is if there is a cleaner way to get a random subset of the dyad-level dataset? Thank you! Jim
... View more