topic Re: proc sql terminology in SAS Enterprise Guide

proc sql terminology

Dsquared — Fri, 10 Nov 2017 15:06:29 GMT

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!!!

Re: proc sql terminology

Kurt_Bremser — Fri, 10 Nov 2017 15:40:17 GMT

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.

Re: proc sql terminology

Jeremy_Browne — Fri, 10 Nov 2017 15:49:19 GMT

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.

Re: proc sql terminology

PGStats — Fri, 10 Nov 2017 22:48:57 GMT

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.

Re: proc sql terminology

PGStats — Fri, 10 Nov 2017 22:54:55 GMT

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.