/* More examples with PROC SQL */

/* The hospital data */
/* Joining tables in PROC SQL */

PROC SQL;
CREATE TABLE hospitnew
(patient num,
 date date,
 pulse num,
 temp num,
 bps num,
 lastname char(10));
 INSERT INTO hospitnew
 VALUES(2004101 '03NOV2005'd 73 98.3 88 'Jones     ')
 VALUES(2004101 '10NOV2005'd 77 98.5 82 'Jones     ')
 VALUES(2004101 '17NOV2005'd 75 98.2 85 'Jones     ')
 VALUES(2004102 '03NOV2005'd 83 98.0 98 'Montgomery')
 VALUES(2004102 '10NOV2005'd 81 98.5 94 'Montgomery')
 VALUES(2004103 '27OCT2005'd 77 99.3 78 'Thomas    ')
 VALUES(2004103 '03NOV2005'd 76 98.5 79 'Thomas    ')
 VALUES(2004103 '17NOV2005'd 79 99.2 75 'Thomas    ')
 VALUES(2004104 '10NOV2005'd 72 98.9 83 'Darhouse  ');

TITLE;

SELECT *
FROM hospitnew;

CREATE TABLE DOSING
(patient num,
 date date,
 med char(5),
 doses num,
 amt num,
 unit char(2));
 INSERT INTO DOSING
 VALUES(2004102 '03NOV2005'd 'Med A' 3 1.4 'mg')
 VALUES(2004102 '10NOV2005'd 'Med A' 2 2.4 'mg')
 VALUES(2004103 '02NOV2005'd 'Med B' 2 2.5 'mg')
 VALUES(2004103 '09NOV2005'd 'Med B' 1 3.1 'mg')
 VALUES(2004103 '16NOV2005'd 'Med B' 3 2.8 'mg')
 VALUES(2004104 '10NOV2005'd 'Med A' 3 3.6 'mg')
 VALUES(2004105 '01NOV2005'd 'Med B' 2 2.5 'mg')
 VALUES(2004105 '08NOV2005'd 'Med B' 2 1.9 'mg')
 VALUES(2004105 '15NOV2005'd 'Med B' 1 3.7 'mg');

SELECT *
FROM DOSING;

QUIT;


/* Inner join:  
only observations with matching PATIENT and DATE values are selected */


PROC SQL; 
CREATE TABLE BOTH AS
SELECT A.PATIENT, A.DATE FORMAT DATE7. AS DATE, A.PULSE,
       B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM HOSPITNEW A INNER JOIN DOSING B
ON (A.PATIENT = B.PATIENT) AND (A.DATE = B.DATE)
ORDER BY PATIENT, DATE;

SELECT *
FROM BOTH;

QUIT;

/* Inner join:  Only on PATIENT */
/* Cartesian product: all possible combinations 
of table A values and table B values */

PROC SQL; 
CREATE TABLE BOTH2 AS
SELECT A.PATIENT, A.DATE FORMAT DATE7. AS DATE, A.PULSE,
       B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM HOSPITNEW A INNER JOIN DOSING B
ON (A.PATIENT = B.PATIENT)
ORDER BY PATIENT, DATE;

SELECT *
FROM BOTH2;

QUIT;

/* Left join */
/* All observations from "left" table;
only matching observations from "right" table */

PROC SQL;
CREATE TABLE LEFTY AS
SELECT A.PATIENT, A.DATE FORMAT DATE7. AS DATE, A.PULSE,
       B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM HOSPITNEW A LEFT JOIN DOSING B
ON (A.PATIENT = B.PATIENT)
ORDER BY PATIENT, DATE;

SELECT *
FROM LEFTY;

QUIT;

/* Right join */
/* All observations from "right" table;
only matching observations from "left" table */

PROC SQL;
CREATE TABLE RIGHTY AS
SELECT A.PATIENT, A.DATE FORMAT DATE7. AS DATE, A.PULSE,
       B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM HOSPITNEW A RIGHT JOIN DOSING B
ON (A.PATIENT = B.PATIENT)
ORDER BY PATIENT, DATE;

SELECT *
FROM RIGHTY;

QUIT;

/* Full join */


PROC SQL;
CREATE TABLE FULLTBL AS
SELECT A.PATIENT, A.DATE FORMAT DATE7. AS DATE, A.PULSE,
       B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM HOSPITNEW A FULL JOIN DOSING B
ON (A.PATIENT = B.PATIENT)
ORDER BY PATIENT, DATE;

SELECT *
FROM FULLTBL;

QUIT;