SAS/Proc SQL

SAS seminar, October 2004 – MEB, KI

Gustaf Edgren
Gustaf.edgren@meb.ki.se

Terminology
SAS Data step Proc SQL

Dataset Table
Variable Column
Observation Row
Merge Join
Append Union

Example database

Death Residence
Cancer
PK,FK1 IDNr PK,FK1 IDNr
InPatient PK InDate Laboration
PK,FK1 IDNr
PK,FK1 IDNr UlCause PK DiagDate PK,FK1 IDNr
MultCause1 OutDate PK LabDate
Hospital MultCause2 County ICD7
Clinic MultCause3 ICD8 LabType
InDate MultCause4 ICD9 LabResult
OutDate Autopsy ICD10 Origin
MainDiag ICD Autopsy
Diagnoses Persons DiagBase
ICD
ExtCause PK IDNr
Operations
Planned BirthDate
InType Sex
OutType DeathDate
Contract MigDate
BloodGroup
InPatOut
InPatCov

TransLocation
PK,FK1 ProductID

Product FK1 TransDate

Donation
FK1 IDNr
PK,FK1,FK2 IDNr PK ProductID Transfusion Hospital
PK DonationID Clinic
FK1 DonationID PK,FK1 ProductID Department
DonDate ProdCode
Donor xProdCode FK3 IDNr
DonType
UnitNum TransDate
PK,FK1 IDNr DonPlace FK2 DonationID
xDonType ProdDate
UnitNum TransCompat
DonDesc ExpireDate
DonDate1 PoolNum
xDonDesc Volume PK,FK1 ProductID
Donations ProdCode
HbValue VolumeUnit
Flag xProdCode
HbType ConsCode TransDate
xConsCode Flag IDNr
Flag
Flag Origin CompatCode
Origin
Origin CompatResult
CompatID
Syntax
Retrieving data
proc sql;
create table tablename as
select [distinct]
column1,
column2,
[*], …
from library.table
where expression
order by column1 etc.;
quit;

Example 1
proc sql;
create table men as
select *
from cblood.persons
where sex = 1;
quit;

Example 2
proc sql;
create table men as
select
idnr,
birthdate
from cblood.persons
where sex = 1
order by birthdate;
quit;

Example 3
proc sql;
create table patient as
select
distinct idnr
from cblood.transfusion;
quit;

Modifying/creating columns
proc sql;
create table tablename as
select
function(column1) as newcolumn1,
column2 [+|-|*|/] column3 as newcolumn2,
…
from library.table;
quit;
Example 4
proc sql;
create table dead as
select
idnr,
(deathdate-birthdate)/365.25 as deathage
from cblood.transfusion
where deathdate ^= .;
quit;

Example 5
proc sql;
create table blc as
select distinct
substr(donationid,2,3) as blc
from cblood.donation;
quit;

Summary functions
proc sql;
create table tablename as
select function(*) as alias
from libname.table
group by byvariable1
having conditions;
quit;

Example 6
proc sql;
create table donations as
select
idnr,
count(*) as count
from cblood.donation
group by idnr;
quit;

Example 7
proc sql;
create table toomany as
select
idnr,
year(dondate) as year
from cblood.donation
where sex=1
group by idnr, year(dondate)
having count(*) > 4
quit;
Combining tables
proc sql;
create table tablename as
select
[alias1.column, alias2.column, *, etc.]
from
libname.table1 as alias1, libname.table2 as alias2
where alias1.column=alias2.column;
quit;

Example 8
proc sql;
create table donationage as
select
a.idnr,
a.dondate,
%age(a.dondate, b.birthdate) as age
from cblood.donation as a,
cblood.persons as b
where a.idnr=b.idnr;
quit;

Combining tables 2
proc sql;
create table tablename as
select
[alias1.column, alias2.column, *, etc.]
from
libname.table1 as alias1
[inner | outer | left | right] join
libname.table2 as alias2
on alias1.column=alias2.column;
quit;

Example 9
proc sql;
create table cancerdonor as
select
a.idnr,
(max(dondate)-min(dondate))/ 365.25 as dontime,
b.icd7,
b.diadate
from cblood.donation as a
left join cblood.cancer as b
on a.idnr=b.idnr
group by a.idnr, b.icd7, b.diadate;
quit;

Age macro
%macro age(date,birth);
floor((intck('month',&birth,&date)-(day(&date)<day(&birth)))/12)
%mend age;