Ten Great Reasons to Learn SQL Procedure/ HOW TO WORK WITH SUBQUERY IN THE SQL PROCEDURE

Ten Great Reasons to Learn SQL Procedure

Top 10 List
# 1. Using the Pass-Through Facility
# 2. Joining Tables of Data
# 3. Creating and Using Views
# 4. Producing Reports
# 5. Using Summary Functions
# 6. Creating and Modifying Tables
# 7. Grouping Data
# 8. Ordering Data
# 9. Subsetting Data
# 10. Retrieving (Querying) Data

source: ssc.utexas.edu



WORKING WITH SUBQUERY IN THE SQL PROCEDURE

Example 1: Find patients who have records in table DEMO but not in table VITAL.

Proc SQL;
Select patid from demog
Where patid not in (select patid from vital);QUIT;
Result:
PATID
-----
150

Example 2: Find patients who didn’t have their blood pressures measured twice at a visit.
Proc SQL;
select patid from vital T1
where 2>(select count(*)
from vital T2
where T1.patid=T2.patid
and
T1.visid=T2.visid);QUIT;
Result:
PATID
-----
110
140

Example 3: Find patients who have the maximum number of visits.
Proc SQL;
Select distinct patid
from vitmean as a
Where not exists
(select visid from vitmean
Except
Select visid from vitmean as b
Where a.patid=b.patid);QUIT;
Result:
PATID
-----
120

Example 4: Find patients whose age is in the average age +/- 5 years.
Proc SQL;
Select patid
from demog
where age between
(select Avg(age)
from demog)-5
and
(select avg(age)
from demog)+5; QUIT;
Result:
PATID
------
110

Example 4: Find patients who didn’t have maximum number of visits.
Proc SQL;
Select patid
From vitmean as a
Group by patid
Having count(visid)
<(select max(cnt) from (select count(visid) as cnt from vitmean group by patid) ); QUIT; Result:
PATID
------
100
110
130
140

Example 5: Calculate the interval between this visit and previous visit for each patient visit.

proc sql;
select a.patid, a.visid,
intck('day', b.visdate, a.visdate)
as interval
from
(select distinct patid,
visid, visdate
from vital
where visid>'1') as a,
(select distinct patid,
visid, visdate
from vital
where visid<'4') as b where(input(a.visid,2.)-1 =input(b.visid,2.)) and a.patid=b.patid order by a.patid, a.visid; QUIT; Result:
PATID VISID INTERVAL
----------------------------
100 2 7
100 3 7
120 2 8
120 3 6
120 4 34
130 2 35
130 3 38
140 2 9

Example 6: Find patients who have exact same visits.
proc sql;
create table vital as
select distinct patid, visid
from vital;
select T1.PATID as PATID,
T2.PATID as PATID1
FROM
(select patid, visid from VITAL)
AS T1
inner join
(select patid, visid from VITAL)
AS T2
on T1.visid=T2.visid
and T1.patid < patid="T1.PATID)" patid="T2.PATID);" color="#cc33cc">RESULT:
PATID PATID1
-----------------
100 130


APPENDIX: EXAMPLE SAS DATA SETS For The ABOVE PROGRAMS:

Data demog;
input patid $1-3 age 5-6 sex $ 8;
datalines;
100 34 M
110 45 F
120 67 M
130 55 M
140 40 F
150 38 M
;
run;

data vital;
input patid $
visid $
visdate mmddyy10.
diabp
sysbp;
format visdate mmddyy10.;
datalines;
100 1 01/12/97 66 110
100 1 01/12/97 68 110
100 2 01/19/97 66 .
100 2 01/19/97 66 110
100 3 01/26/97 68 120
100 3 01/26/97 68 120
110 1 02/03/97 68 110
110 3 02/12/97 64 115
110 3 02/12/97 68 115
120 1 04/05/97 66 105
120 1 04/05/97 64 105
120 2 04/13/97 110 64
120 2 04/13/97 105 82
120 3 04/19/97 63 105
120 3 04/19/97 105 90
120 4 05/23/97 90 106
120 4 05/23/97 92 108
130 1 02/12/97 66 111
130 1 02/12/97 67 110
130 2 03/19/97 66 109
130 2 03/19/97 66 110
130 3 04/26/97 68 121
130 3 04/26/97 68 118
140 1 02/03/97 68 110
140 2 02/12/97 64 115
140 2 02/12/97 68 115
;
run;

source: www.nesug.org/proceedings/nesug98/dbas/p005.pdf