11-10-2017 10:06 AM
I am using proc sql to combine datasets. What is the theoretical difference (if any) in using these two codes with respect to the patient IDs kept? Do they both only keep the patient IDs from the first dataset and match to the identical patient IDs in the second? The goal of both is to use the a dataset and then only obtain the files with the same patient ID showing up in b.
create table mart_TS.missmedical as
select a.patid, a.index_dt, a.preg_dt, b.*
from mart_TS.outcomemiss a, dbname.clinical(keep=patid eventdate medcode) b
order by a.patid, b.medcode;
create table mart_TS.outcomemiss as
from mart_tmp.ptlist3bdedup where patid not in (select distinct patid from mart_tmp.ptlist4bdedup);
11-10-2017 10:40 AM
Try it and compare the results. See Maxim 4.
If the results are equal, check real and cpu times in the log to determine which method is more efficient.
11-10-2017 10:49 AM
proc sql; Select a.id, a.var2, b.* From tablea a, tableb b Where a.id = b.id;
Above performs an ‘Inner join’ of all records in a and b, matched on id, with columns in select list from both tables and potentially multiple rows for a single id from tablea if multiple matches in tableb (i.e. one to many relationship)
proc sql; Select * From tablea where id in (select distinct id from tableb);
Whereas, latter returns all records from tablea with an id that exists in tableb (or the complement, i.e. returns if doesn’t exist, if NOT IN used). WHERE IN (sub-query) does not join the tables and you cannot select other variables from tableb in your select list. Also, if you have a one to many relationship between the two tables, using a sub-query will only return one row per record from tablea.
11-10-2017 05:48 PM
The IDs from the first query are a subset of the IDs kept by the second query since the first query selects only IDs from the second query that are also present in table dbname.clinical.
11-10-2017 05:54 PM
Note, the first query might generate more records than you expect if there are multiple records per ID in dbname.clinical.
The second query doesn't have that problem (even without the distinct keyword) but it doesn't allow you to bring in fields from the second table.