A relational database consists of data stored in tables where the columns represent varaibles and the rows represent observations. SQL (structured query language) is a language for navigating and manipulating the database and tables. Today, we will introduce some basic SQL and compare it with DATA and PROC step code to perform the same actions.
The most used SQL statement is the SELECT statement, so we will start with SELECT and its substatements. The basic syntax is as follows:
PROC SQL; SELECTFROM WHERE GROUP BY columns HAVING expressions ORDER BY columns; QUIT;
Not all of these statements need to be used each time, but they must be used in this order.
LIBNAME mydata "H:\BiostatCourses\PublicHealthComputing\Lectures\Week9MultipleReg\SAS";
PROC CONTENTS DATA=mydata.hers;
RUN;
/*
PROC PRINT DATA=mydata.hers (OBS=5);
VAR BMI HT age smoking;
RUN;
*/
PROC SQL OUTOBS=5;
SELECT BMI, HT, age, smoking
FROM mydata.hers
QUIT;
The SELECT statment chooses which columns (varaibles) you want from the table(s) listed in the FROM statement. The OUTOBS=5 limits the number of rows produced in the created view to 5 rows. We can also add labels and apply formats.
/*
DATA mydata.hers;
SET mydata.hers;
LABEL BMI="Body Mass Index"
HT="Hormone Therapy"
age="Age (yrs)
smoking="Smoking Status";
FORMAT BMI 7.2;
RUN;
PROC PRINT DATA=mydata.hers (OBS=5);
VAR BMI HT age smoking;
RUN;
*/
PROC SQL OUTOBS=5;
SELECT BMI LABEL="Body Mass Index" FORMAT=7.2,
HT LABEL="Hormone Therapy",
age LABEL="Age (yrs)",
smoking LABEL="Smoking Status"
FROM mydata.hers;
QUIT;
We can also create new variables.
/*
DATA mydata.hers;
SET mydata.hers;
IF BMI <= 18.5 THEN BMICat='Low';
ELSE IF 18.5 < BMI <=24.9 THEN BMICat='Normal';
ELSE IF 24.9 < BMI <= 29.9 THEN BMICat='Overweight';
ELSE BMICat='Obese';
age10=age/10;
RUN;
PROC PRINT DATA=mydata.hers (OBS=5);
VAR BMI BMICat age age10;
RUN;
*/
PROC SQL OUTOBS=5;
SELECT BMI,
CASE
WHEN BMI <= 18.5 THEN 'Low'
WHEN 18.5 < BMI <=24.9 THEN 'Normal'
WHEN 24.9 < BMI <= 29.9 THEN 'Overweight'
ELSE 'Obese'
END AS BMICat,
age,
age/10 AS age10
FROM mydata.hers;
QUIT;
To subset data, we can use the WHERE clause.
/*
PROC PRINT DATA=mydata.hers (OBS=5);
VAR BMI age HT;
WHERE smoking="yes" AND drinkany="yes";
RUN;
*/
PROC SQL OUTOBS=5;
SELECT BMI, AGE, HT
FROM mydata.hers
WHERE smoking="yes" AND drinkany="yes";
QUIT;
PROC SQL can also sort data using the ORDER by clause.
/*
PROC SORT DATA=mydata.hers;
VAR smoking drinkany DBP desc;
RUN;
PROC PRINT DATA=mydata.hers (OBS=10);
VAR smoking drinkany dbp;
RUN;
*/
PROC SQL OUTOBS=10;
SELECT SMOKING, DRINKANY, DBP
FROM mydata.hers
ORDER BY smoking, drinkany, DBP desc;
QUIT;
/*
DATA Site1;
INPUT PatientId SBP DBP SEX $;
DATALINES;
100 117 75 Male
101 118 80 Female
102 110 71 Female
;
RUN;
DATA Site2;
INPUT PatientId SBP DBP;
DATALINES;
103 125 75
104 106 79
105 127 82
;
RUN;
*/
PROC SQL;
CREATE TABLE Site1
(PatientId num,
SBP num,
DBP num,
Sex CHAR(7));
INSERT INTO SITE1
values(100,117,75,"Male")
values(101,118, 80,"Female")
values(102,110,71,"Female");
CREATE TABLE Site2
(PatientId num,
SBP num,
DBP num);
INSERT INTO SITE2
SET PatientId=103, SBP=125, DBP=75
SET PatientId=104, SBP=106, DBP=79
SET PatientId=105, SBP=127, DBP=82;
SELECT * FROM SITE1;
SELECT * FROM SITE2;
QUIT;
The following creates a new table from Site1 and Site2 by stacking them.
/*
DATA MasterSite;
SET Site1 Site2;
RUN;
PROC PRINT DATA = MasterSite;
RUN;
*/
PROC SQL;
CREATE TABLE MasterSite AS
SELECT COALESCE(Site1.PatientId,Site2.PatientId) AS PatientId,
COALESCE(Site1.SBP,Site2.SBP) AS SBP,
COALESCE(Site1.DBP,Site2.DBP) AS DBP,
SEX
FROM Site1 FULL JOIN Site2
ON Site1.PatientId=Site2.PatientId;
SELECT * FROM MASTERSITE;
QUIT;
DATA Site3;
INPUT PatientId SBP DBP Visit MMDDYY8.;
DATALINES;
100 117 75 12/15/16
101 118 80 12/10/16
102 110 71 12/3/16
;
RUN;
DATA Site4;
INPUT PatientId Weight Height;
DATALINES;
100 125 60
101 140 67
105 180 70
;
RUN;
/*
PROC SORT DATA = Site3;
BY PatientId;
RUN;
PROC SORT DATA = Site4;
BY PatientId;
RUN;
DATA Master2;
MERGE Site3 Site4;
BY PatientId;
RUN;
PROC PRINT DATA = Master2;
TITLE2 "Merged Dataset";
RUN;
*/
PROC SQL;
SELECT *
FROM Site3, Site4
WHERE Site3.PatientId=Site4.PatientId;
SELECT COALESCE(Site3.PatientId,Site4.PatientId) AS PatientId,
Site3.SBP,Site3.DBP,Site3.Visit FORMAT=MMDDYY8.,
SITE4.Weight, Site4.Height
FROM SITE3 FULL JOIN SITE4
ON Site3.PatientId=Site4.PatientId;
QUIT;
DATA PATIENTS;
INFILE "H:\BiostatCourses\PublicHealthComputing\Lectures\Week5DataManagement\SAS\patients.txt" PAD;
INPUT @1 PATNO $3.
@4 GENDER $1.
@5 VISIT MMDDYY10.
@15 HR 3.
@18 SBP 3.
@21 DBP 3.
@24 DX $3.
@27 AE $1.;
LABEL PATNO = "PATIENT NUMBER"
GENDER = "GENDER"
VISIT = "VISIT DATE"
HR = "HEART RATE"
SBP = "SYSTOLIC BLOOD PRESSURE"
DBP = "DIASTOLIC BLOODPRESSURE"
DX = "DIAGNOSIS CODE"
AE = "ADVERSE EVENT?";
FORMAT VISIT MMDDYY10.;
RUN;
/*
PROC FREQ DATA = Patients;
TABLE GENDER / NOCUM NOPERCENT;
RUN;
*/
PROC SQL;
SELECT GENDER, COUNT(GENDER)
FROM PATIENTS
GROUP BY GENDER;
QUIT;
/*
PROC SORT DATA=Patiets;
VAR GENDER;
RUN;
PROC MEANS DATA=Patients MEAN;
VAR SBP HR;
BY GENDER;
WHERE DBP > 60;
RUN;
*/
PROC SQL;
SELECT GENDER, MEAN(SBP) AS AvgSBP, MEAN(HR) as AvgHR
FROM PATIENTS
WHERE DBP > 60
GROUP BY GENDER;
QUIT;