/* Editing and Altering Data Tables with 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  ');

/* Adding New values to a table */

PROC SQL;
INSERT INTO hospitnew
VALUES(2004104 '17NOV2005'd 74 98.6 94 'Darhouse  ');

INSERT INTO hospitnew
SET PATIENT=2004105, DATE='16NOV2005'd, PULSE=89, 
    TEMP=97.5, BPS=79;
QUIT;

*lastname is set to missing since a value wasn't provided ;

/* Deleting observations from a table */

PROC SQL;
DELETE FROM hospitnew
WHERE PATIENT=2004101;

SELECT *
FROM hospitnew;

QUIT;

/* Updating observations in a table */

PROC SQL;
UPDATE hospitnew
SET PULSE=79, BPS=89
WHERE PATIENT=2004105 and DATE='16NOV2005'd;

SELECT *
FROM hospitnew;

QUIT;

/* Changing column formats
and dropping a column */

PROC SQL;
ALTER TABLE hospitnew
MODIFY DATE FORMAT=MMDDYY8.
DROP TEMP;

SELECT *
FROM hospitnew;

QUIT;

/* Creating a blood pressure table ... */

PROC SQL;
CREATE TABLE BLOODP AS
SELECT patient, date, bps
FROM hospitnew;
QUIT;

/*... and deleting it */
PROC SQL;
DROP TABLE BLOODP;
QUIT;

/* Viewing the contents of our table: */

PROC SQL;
SELECT *
FROM DICTIONARY.COLUMNS
WHERE UPCASE(LIBNAME) = 'WORK' AND 
      UPCASE(MEMNAME) = 'HOSPITNEW';
QUIT;