/* More examples with PROC SQL */

/* Creating a table from scratch using PROC SQL statements */


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 'Hospital Data';

SELECT *
FROM hospitnew;
QUIT;

/* Subsetting and Calculating in PROC SQL*/

PROC SQL;
CREATE TABLE BLTEMP AS
SELECT patient, date FORMAT=DATE9., bps, (temp-32)/9*5 AS tempc
FROM hospitnew
 WHERE patient IN (2004101 2004103)
 ORDER BY patient, date DESCENDING;

SELECT *
FROM BLTEMP;
QUIT;

/* Calculations for the whole data set */

PROC SQL;
CREATE TABLE MEANBLTEMP AS
SELECT COUNT(*) AS N, ROUND(MEAN(bps), 0.1) FORMAT=6.1 AS MEANBPS,
                   ROUND(MEAN(temp), 0.01) FORMAT=6.2 AS MEANTEMP
FROM hospitnew;

SELECT *
FROM MEANBLTEMP;
QUIT;

/* Calculations for each patient */

PROC SQL;
CREATE TABLE HIGHBLD AS
SELECT PATIENT, COUNT(PATIENT) AS N, DATE FORMAT=DATE7., BPS,
       MAX(BPS) AS BPSHIGH, ROUND(BPS/(CALCULATED BPSHIGH)*100, 0.01) FORMAT=6.2 AS BPSPCT
FROM hospitnew
GROUP BY PATIENT;

SELECT *
FROM HIGHBLD;
QUIT;

/* Calculations for each patient */
/* Only list those measurements where 
blood pressure is "high" */
/* Cannot use WHERE statement on "calculated" variables */
/* Instead, use the HAVING keyword */

PROC SQL;
CREATE TABLE HIGHBLD2 AS
SELECT PATIENT, COUNT(PATIENT) AS N, DATE FORMAT=DATE7., 
       BPS, MAX(BPS) AS BPSHIGH
FROM hospitnew
WHERE PATIENT IN (2004101 2004102 2004103)
GROUP BY PATIENT
HAVING BPS = CALCULATED BPSHIGH
ORDER BY CALCULATED BPSHIGH;

SELECT *
FROM HIGHBLD2;
QUIT;

/* Using the CASE statement */

PROC SQL;
CREATE TABLE TESTMED AS
SELECT PATIENT,
CASE ((PATIENT/2 = INT(PATIENT/2)) + (PATIENT = .))
WHEN 1 THEN 'Med A'
WHEN 0 THEN 'Med B'
ELSE 'Error' END AS DOSEGRP
LENGTH=5
FROM hospitnew
ORDER BY PATIENT;

/* Value of CASE expression could be 1 (if patient # even), 0 (if patient # odd),
or 2 (if patient # missing) */

SELECT *
FROM TESTMED;
QUIT;