/* More examples with PROC SQL */

/* The hospital data */
/* Joining tables in PROC SQL */
/* Two tables, hospitnew and dosing, are created below */

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: This is a join on "complete" cases, in which          */ 
/* only observations with matching PATIENT and DATE values are selected */
/* The FROM statement creates aliases A and B which simplifies   */
/* variable labels. Note that B.DATE and B.PATIENT are implicitly defined */

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;

/* Skip  */
/* Inner join:  Only on PATIENT */
/* This creates a Cartesian product: all possible combinations of table A and   */
/* table B values with common patients. Not recommended.                        */


PROC SQL;

*Display tables again to help visualize the join;
SELECT * FROM HOSPITNEW; 
SELECT * FROM DOSING; 
 
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, which includes all records from A along with matching */
/* records from B  */

PROC SQL;

*Display tables again to help visualize the join;
SELECT * FROM HOSPITNEW; 
SELECT * FROM DOSING; 

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) AND (A.DATE=B.DATE)
ORDER BY PATIENT, DATE;

SELECT *
FROM LEFTY;

QUIT;

/* Right join */
/* All observations from "right" table; only matching observations from "left" table */
/* Note that SELECT had to be changed to read PATIENT from B */
/* P_ID is analagous to IN= index for the right table   */

PROC SQL;

*Display tables again to help visualize the join;
SELECT * FROM HOSPITNEW; 
SELECT * FROM DOSING; 

CREATE TABLE RIGHTY AS
SELECT b.patient, b.date format date7. as DATE, 
       A.PULSE, B.MED, B.DOSES, B.AMT FORMAT=4.1,
       (b.patient=a.patient) as p_ind
FROM HOSPITNEW A RIGHT JOIN DOSING B
ON (A.PATIENT = B.PATIENT) and (A.DATE=B.DATE)
ORDER BY PATIENT, DATE;

SELECT *
FROM RIGHTY;

QUIT;

/* Full join--cartesian product plus unique cases from each table  */
/* Use COALESCE so that important patient and date info is not discarded     */

PROC SQL;

*Display tables again to help visualize the join;
SELECT * FROM HOSPITNEW; 
SELECT * FROM DOSING; 

CREATE TABLE FULLTBL AS
SELECT coalesce(a.patient,b.patient) as patient_id, 
       coalesce(a.date,b.date) as combined_date format date7.,
       A.PULSE, B.MED, B.DOSES, B.AMT FORMAT=4.1
FROM HOSPITNEW A FULL JOIN DOSING B
ON (A.PATIENT = B.PATIENT) and (A.DATE=B.DATE)
ORDER BY PATIENT_ID, COMBINED_DATE;

select * from fulltbl;
QUIT;