/* More examples with PROC SQL */

/* Creating a table from scratch using PROC SQL statements */
/* Variable definition isn't bad, but data entry is inconvenient */

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*/
/* We are defining a date format, and carrying out some simple math */

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;

/* Summary statistics for the whole data set--hence the use of count(*)  */
/* count(*) means to compute the count over the entire 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 */
/* Note use of CALCULATED for BPSHIGH since BPSHIGH was not part of
the original data set   */

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;

/* Summary Calculations for each patient   */
PROC SQL;
CREATE TABLE HIGHBLD AS
SELECT PATIENT, COUNT(PATIENT) AS N,
       MAX(BPS) AS BPSHIGH
FROM hospitnew
GROUP BY PATIENT;

SELECT *
FROM HIGHBLD;
QUIT;

/* Summary Calculations for each patient. Adding BPS back into the data generates */
/* a complete set of records again */
PROC SQL;
CREATE TABLE HIGHBLD AS
SELECT PATIENT, COUNT(PATIENT) AS N, BPS,
       MAX(BPS) AS BPSHIGH
FROM hospitnew
GROUP BY PATIENT;

SELECT *
FROM HIGHBLD;
QUIT;

/* Calculations for each patient */
/* Only list the highest blood pressure measurements for each patient */
/* 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;

/* Skip */
/* Using the CASE statement */
/* The CASE statement here is complex--it basically determines whether */
/* a patient ID is odd, even, or missing                               */

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;