Outline Background SQL history and terminology Introduction - PDF document

SAS /Proc SQL SAS /Proc SQL Outline • Background – SQL history and terminology • Introduction SAS seminar – Proc SQL – Proc SQL – Intro to SQL 2004-10-26 • Syntax – Retrieving data – Modifying data – Summary functions Gustaf Edgren – Combining tables MEB, Karolinska Institutet • Conclusion SAS /Proc SQL SAS /Proc SQL Non-outline Background – SQL history • I will NOT cover: • Structured Query Language. • Developed by IBM in the 1970s for querying, – How to create or modify tables modifying and updating relational databases – How to append tables • Adopted and standardized by ANSI and ISO – How to perform sub-queries during the 1980s – How to work with views • Used with almost all RDBMS (Relational Data – Database specific issues like relational Base Management System) e.g. Oracle, DB2, integrity, primary keys etc. Access etc. • Most RDBMS also offer enhancements to ANSI …Maybe next time? SQL 1

SAS /Proc SQL SAS /Proc SQL Background - terminology Background – example database Death Residence SAS Data step Proc SQL Cancer PK,FK1 IDNr PK,FK1 IDNr InPatient Laboration PK InDate PK,FK1 IDNr UlCause PK,FK1 IDNr PK DiagDate PK,FK1 IDNr MultCause1 OutDate PK LabDate MultCause2 County Hospital ICD7 MultCause3 ICD8 Clinic LabType MultCause4 InDate ICD9 LabResult Dataset Table Autopsy OutDate ICD10 Origin ICD Autopsy MainDiag Diagnoses DiagBase Persons ICD PK IDNr ExtCause Operations BirthDate Planned Variable Column InType Sex DeathDate OutType MigDate Contract BloodGroup InPatOut InPatCov TransLocation Observation Row PK,FK1 ProductID FK1 TransDate Product Donation FK1 IDNr PK ProductID PK,FK1,FK2 IDNr Transfusion Hospital PK DonationID Clinic FK1 DonationID PK,FK1 ProductID Merge Join Department ProdCode DonDate Donor xProdCode FK3 IDNr DonType TransDate PK,FK1 IDNr UnitNum DonPlace FK2 DonationID xDonType ProdDate TransCompat ExpireDate UnitNum DonDate1 DonDesc PoolNum PK,FK1 ProductID Volume Donations xDonDesc VolumeUnit ProdCode Append Union HbValue Flag xProdCode ConsCode TransDate HbType Flag IDNr xConsCode Flag Flag Origin Origin CompatCode Origin CompatResult CompatID SAS /Proc SQL SAS /Proc SQL Introduction – what is Proc SQL? Introduction – syntax • Proc SQL is the SAS implementation of SQL • Very straightforward and extremely • Proc SQL is a powerful SAS procedure that structured syntax combines the functionality of the SAS data step • Highly scalable, from simple one row with the SQL language queries, to complicated queries with • Proc SQL can sort, subset, merge and summarize data – all at once several sub-queries • Proc SQL can combine standard SQL functions • Possible to combine with all SAS functions with virtually all SAS functions (except the lag function) • Proc SQL can work remotely with RDBMS:s such as Oracle 2

SAS /Proc SQL SAS /Proc SQL Retrieving data Retrieving data – syntax proc sql ; • The simplest SQL queries only retrieve create table tablename as and subset data from a specified source select [distinct] column1 , column2 , • This is can usually be achieved just as [*], … easily with SAS code from library.table where expression order by column1 etc. ; • Some advantages with SQL does however quit; exist… * = all columns SAS /Proc SQL SAS /Proc SQL Example 1 Example 1 SQL approach SAS approach • With our example database in mind, how proc sql ; would one create a table with all the men data men; create table men as in the database? set cblood.persons; select * where sex=1; from cblood.persons run; where sex = 1; quit; 3

SAS /Proc SQL SAS /Proc SQL Example 2 • What if we wanted a table with all men and their birthdates; sorted ascending by SAS – SQL birthdate? 1 – 0 SAS /Proc SQL SAS /Proc SQL Example 2 SQL approach SAS approach proc sql ; data men; create table men as set cblood.persons; SAS – SQL select where sex=1; idnr, keep idnr birthdate; birthdate run; 1 – 1 from cblood.persons where sex = 1 proc sort data=men; order by birthdate; * by birthdate; quit; run; * Sort performed by data source 4

SAS /Proc SQL SAS /Proc SQL Example 3 Example 3 SQL approach SAS approach • Well then, that was simple (?), what if we proc sql; data patient; wanted a list of all the people that ever create table patient as set cblood.transfusion; received blood? select keep idnr; distinct idnr from cblood.transfusion; run; quit; proc sort data=patient nodup; by idnr; run; SAS /Proc SQL SAS /Proc SQL Modifying columns • SQL, just like SAS, offers the possibility to create new columns (variables) with: SAS – SQL – New values – Other columns (variables) – Combinations of new values and other 1 – 2 columns – SAS and/or SQL functions applied to any value or column 5

SAS /Proc SQL SAS /Proc SQL Modifying columns – syntax Example 4 proc sql ; • Lets say we want a table with the age (in create table tablename as years) at death of all the people in our cohort that have died this far select function( column1) as newcolumn1 , – Simple? column2 [+|-|*|/] column3 as newcolumn2 , … from library.table; quit; SAS /Proc SQL SAS /Proc SQL Example 4 SQL approach SAS approach proc sql; data dead(keep=idnr create table dead as deathage); SAS – SQL select set cblood.transfusion idnr, (keep=idnr birthdate (deathdate-birthdate) deathdate); /365.25 as deathage where deathdate ^=.; 2 – 3 from cblood.transfusion deathage=(deathdate- where deathdate ^= .; birthdate) /365.25; quit; run; 6

SAS /Proc SQL SAS /Proc SQL Example 5 Example 5 SQL approach SAS approach • So, what if we want to use a SAS proc sql; data blc(keep=blc); function? How do we do that? create table blc as set cblood.donation • Lets extract the blood central ID from the (keep=donationid); select distinct donation ID to see what blood centrals blc=substr(donationid,3,3); substr(donationid,2,3) have been involved as blc run; from cblood.donation; proc sort data=blc nodup; quit; by blc; run; SAS /Proc SQL SAS /Proc SQL Summary functions • SQL also has the ability to summarize data SAS – SQL • Counts, means, etc are easily calculated and presented or stored in new or existing tables 3 – 4 7

SAS /Proc SQL SAS /Proc SQL Summary functions – syntax Example 6 proc sql; • Lets say we want to calculate the total create table tablename as number of donations per person. select function(*) as alias • How does one do that? from libname.table group by byvariable1 having conditions; quit; SAS /Proc SQL SAS /Proc SQL Example 6 SQL approach SAS approach proc sql; data temp; create table donations as set cblood.donation; select keep idnr; SAS – SQL idnr, proc freq; count(*) as count table idnr / out=donations(keep=idnr count); from cblood.donation run; group by idnr; 3 – 5 quit; 8

SAS /Proc SQL SAS /Proc SQL Example 7 Example 7 SQL approach SAS approach • In order to protect donors, the maximum proc sql; data temp(keep=idnr year); number of whole blood donations one is create table toomany as set cblood.donation(keep=idnr dondate); allowed to make each year is limited to select where sex=1; idnr, four (men) or three (women) year=year(dondate); year(dondate) as year proc freq data=temp noprint; from cblood.donation • Is there a simple way to identify people table idnr*year / out=temp where sex=1 (keep=idnr year count); who have given too many times? group by idnr, year(dondate) data toomany; having count(*) > 4; set temp; quit; where count > 4; run; SAS /Proc SQL SAS /Proc SQL Combining tables • So, what if we want to select data from multiple tables and store it in one table? SAS – SQL • In essence what we want to do is perform a merge, or as it is called in SQL a join 3 – 6 • Lets look at an example; we want to join the person and donation table and calculate age a each donation 9

SAS /Proc SQL SAS /Proc SQL Example 8 Example 8 • The SAS code is simple, but how do we persons donation do it with SQL? idnr idnr birthdate dondate MERGE etc. etc. • Several possible ways exist, lets start with the simplest variant donationage idnr dondate age SAS /Proc SQL SAS /Proc SQL Syntax – combining tables Example 8 SAS approach proc sql; SQL approach proc sql; proc sort data=cblood.donation create table tablename as out=donation; create table donationage as keep idnr dondate; select select by idnr; a.idnr, [ alias1.column, alias2.column , *, etc.] proc sort data=cblood.persons a.dondate, out=persons; from keep idnr birthdate; %age(a.dondate, b.birthdate) as age by idnr; libname.table1 as alias1, libname.table2 as alias2 from cblood.donation as a, data donationage where alias1.column = alias2.column ; merge donation(in=a) cblood.persons as b persons(in=b); where a.idnr=b.idnr; quit; by idnr; quit; if a and b; age=%age(dondate, birthdate); run; 10