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.
proc sql;
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
where a.patid=b.patid
order by a.patid, b.medcode;
quit;
proc sql;
create table mart_TS.outcomemiss as
select *
from mart_tmp.ptlist3bdedup where patid not in (select distinct patid from mart_tmp.ptlist4bdedup);
quit;
Thanks!!!
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.