Proc SQL Code with syntax and examples

SAS Data step Proc SQL

Dataset Table

Variable Column

Observation Row

Merge Join

Append Union

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;