Introduktionskurs i SAS Base

Introduktionskurs i SAS Base
Del 3
Sammanslagning av dataset (joins)
och
grundläggande SAS/SQL
Joins och Proc sql
Detta material skyddas av upphovsrättslagen och får inte kopieras eller på annat
sätt spridas utan upphovsmannens (Knowit Software Solutions AB) skriftliga
godkännande.
Upphovsmannen har gett sitt godkännande att detta material får fritt användas vid
undervisning på Linköpingsuniversitet, IDA.
Sidan 2
2009-01-20
Joins och Proc sql
SAMMANSLAGNING AV DATASET (JOINS).............................................................................................. 4
MERGE ................................................................................................................................................................. 4
Enkel-matchning...................................................................................................................................... 4
Nyckelvariabelsmatchning............................................................................................................................ 5
Tips vid användning av MERGE......................................................................................................... 7
UPDATE............................................................................................................................................................. 8
SET ................................................................................................................................................................... 10
PROC APPEND.................................................................................................................................................... 12
SAS/SQL............................................................................................................................................................. 13
SKAPA TABELLER OCH VYER ............................................................................................................................... 13
CALCULATED ..................................................................................................................................................... 14
OLIKA SÄTT ATT VÄLJA UT DATA ....................................................................................................................... 14
CASE-SATSER.................................................................................................................................................... 15
SKAPA TOM TABELL MED RÄTT ATTRIBUT ........................................................................................................... 16
INSERT INTO .................................................................................................................................................. 16
TA BORT RADER FRÅN TABELL............................................................................................................................ 16
TA BORT EN HEL TABELL ................................................................................................................................... 16
UPPDATERA EN TABELL ...................................................................................................................................... 17
SUBQUERIES ....................................................................................................................................................... 18
SET-operatorer ............................................................................................................................................ 18
SKAPA MACROVARIABLER ................................................................................................................................... 19
AUTOMATISKA MACROVARIABLER ...................................................................................................................... 20
SAMMANSLAGNING AV TABELLER – JOIN ............................................................................................................ 21
Inner join ................................................................................................................................................... 21
Outer join ..................................................................................................................................................... 22
Cartesian product................................................................................................................................... 23
SUMMERINGAR ................................................................................................................................................... 23
INDEX ................................................................................................................................................................ 24
SQL PASSTHRU .................................................................................................................................................. 24
LÄNKAR OCH LITTERATURTIPS ........................................................................................................................... 24
ATT ANVÄNDA SAS METAINFORMATION .............................................................................................. 26
Sidan 3
2009-01-20
Joins och Proc sql
Sammanslagning av dataset (joins)
Att foga ihop information från två eller flera dataset till ett dataset är väldigt
användbart. Detta kan göras på ett flertal sätt. Vi börjar dock med att visa hur du
kan använda dataset-uttrycket MERGE för ihopfogning av två eller flera dataset.
Vid ihopslagning av två eller flera dataset använder man sig av en gemensam
nyckelvariabel, d.v.s. någon ID-variabel. Ifall denna variabel skulle heta olika i de
olika tabellerna, kan man använda sig av RENAME för att döpa om någon av dem för
att alla ska ha samma namn, t.ex.
data ab;
merge a b (rename=(ID=IDNR));
run;
Merge
Merge använts framförallt för att sätta ihop två eller
flera dataset från ”sidan”.
För att illustrera användandet av MERGE så utgår
vi ifrån dessa två dataset innehållande IDNR som
nyckelbegrepp. För att använda nyckelbegreppet
via ett BY-statement måste båda dataseten sorteras
efter nyckelbegreppet.
SAS-datset B
Idnr
1
2
SAS-datset A3
4
Idnr
Datum Lön
6
1
1
2
7
980202
990101
940304
970101
Kön
P-nr
Man
Kvinna
Kvinna
Man
Man
700312
540602
640730
670612
460907
16000
20000
16500
21250
Enkel-matchning
Används väldigt sällan eftersom denna metod är lite vansklig. Denna metod kräver
mycket av den information som ska matchas ihop. Det man bör tänka på är att
observation ett från dataset A matchas ihop med observation ett i dataset B osv. Om
man inte tänker på detta kan det gå som nedan. Om dataseten har någon variabel
gemensam så tas alltid värdet ifrån det sist angivna datasetets variabel till det nya
datasetet.
Sidan 4
2009-01-20
Joins och Proc sql
Exempel – enkel matchning:
SAS-datset AB
data ab;
merge a b;
run;
Idnr
Datum
Lön
Kön
P-nr
1
2
3
4
6
980202
990101
940304
970101
.
16000
20000
16500
21250
.
Man
Kvinna
Kvinna
Man
Man
700312
540602
640730
670612
460907
Denna matchning får ett resultat som är felaktigt eftersom ingen hänsyn tagits till
nyckelvariabeln IDNR. SAS ”bestämde” alltså att den sist angivna datasetets variabel
IDNR skulle användas.
Nyckelvariabelsmatchning
Vad skulle man då göra för att få ordning på matchningen i föregående exempel.
Den gemensamma variabeln i båda dataseten är IDNR, vilken i detta fall får utgöra
nyckelvariabeln. För att lyckas med matchning med hjälp av BY-variabler måste båda
dataseten vara sorterade. Vi testar med BY-variabeln och får följande resultat.
Exempel - Nyckelvariabelsmatchning:
SAS-datset AB
data ab;
merge a b;
by idnr;
run;
Idnr
Datum
Lön
Kön
P-nr
1
1
2
3
4
6
7
980202
990101
940304
.
.
.
970101
16000
20000
16500
.
.
.
21250
Man
Man
Kvinna
Kvinna
Man
Man
700312
700312
540602
640730
670612
460907
.
Detta blev bättre. Här kan man se att rätt person fick rätt lön. De IDNR
(nyckelvariabeln) som inte hittar någon matchning skapar bortfall för de
tillkommande variablerna från det andra datasetet.
Sidan 5
2009-01-20
Joins och Proc sql
Exempel – Matchning för poster från första datasetet med (IN =):
Hur ska man göra om man bara är intresserad av att dataset A ska tillföras nya
uppgifter från dataset B (d.v.s. nya variabler/kolumner, om det finns några). För
detta ändamål får man använda sig av ett speciellt dataset-option [IN = ] som kan
användas för SET, MERGE and UPDATE. Denna option är dock mest användbar för
MERGE. Vi utgår fortfarande ifrån dataset A och B. Denna option används för att
tilldela en ”intern” variabel värdet ETT om observationen kommer ifrån det aktuella
datasetet. Nedan kallar vi dessa variabler A och B. Den fungerar på följande sätt.
SAS-datset AB
data ab;
merge a(in = a) b;
by idnr;
if a;
run;
Idnr
Datum
Lön
Kön
P-nr
1
1
2
7
980202
990101
940304
970101
16000
20000
16500
21250
Man
Man
Kvinna
700312
700312
540602
.
Exempel – Matchning för poster från andra datasetet med (IN =):
SAS-datset AB
data ab;
merge a b(in = b);
by idnr;
if b;
run;
Idnr
Datum
Lön
Kön
P-nr
1
1
2
3
4
6
980202
990101
940304
.
.
.
16000
20000
16500
.
.
.
Man
Man
Kvinna
Kvinna
Man
Man
700312
700312
540602
640730
670612
460907
Exempel - Matchning för poster med by-variabel som finns i båda dataseten:
SAS-datset AB
data ab;
Idnr
merge a(in = a) b(in = b);
by idnr;
1
if a and b;
1
run;
2
Sidan 6
Datum
Lön
980202 16000
990101 20000
940304 16500
Kön
P-nr
Man
Man
Kvinna
700312
700312
540602
2009-01-20
Joins och Proc sql
Exempel - Matchning om IDNR endast finns med dataset A:
data ab;
merge a(in = a) b(in = b); Idnr
by idnr;
if a and not b;
7
run;
SAS-datset AB
Datum
Lön
Kön
970101 21250
P-nr
.
Exempel - Vi vill endast att matchning görs om IDNR endast finns med dataset B:
SAS-datset AB
data ab;
merge a(in = a) b(in = b); Idnr
by idnr;
3
if not a and b;
4
run;
6
Datum
Lön
Kön
P-nr
.
.
.
.
.
.
Kvinna
Man
Man
640730
670612
460907
Tips vid användning av MERGE
Tänk på att aldrig ha samma namn på den interna (IN=) variabeln som någon annan
variabel i datasetet. Efter genomförd matchning tas nämligen dessa interna ’IN’variabler bort, även om de var ”riktiga” variabler innan matchningen gjordes.
Merge kan vara lite lurt ibland. MERGE fungerar så att när en observation har hittat
en matchning är SAS nöjt. Den forsätter sedan inte att försöka matcha
observationen igen om det inte är den sista observationen i BY-gruppen. För det
mesta duger detta men när fler ett dataset innehåller dubblerade nyckelvariabler
kan det gå ”uppåt väggarna” fel. För att klara av detta i datasteg krävs inblandning
av DO-loopar eller macros. Detta är dock tidskrävande och inte speciellt effektivt.
Vad man i detta läge bör studera närmare är SQL som vid en speciell typ av
sammanslagning skapar en katekesisk produkt mellan de ingående indataseten.
Sidan 7
2009-01-20
Joins och Proc sql
UPDATE
Dataset-uttrycket UPDATE använts framförallt om du vill ta bort, lägga till eller ändra
vissa specifika värden i ett dataset. För att rätt poster ska uppdateras används
nyckelvariabeler.
Detta görs via ett BY-uttryck som måste finnas med när UPDATE ska användas. För
att genomföra UPDATE krävs att man har ett huvuddataset (det dataset man vill
uppdatera) samt ett uppdateringsdataset. Det sistnämnda datasetet ska endast
innehålla de värden man vill förändra, de resterande värdena ska i normalfallet vara
missing.
Dataseten måste också vara sorterade i samma ordning, d.v.s. på den nyckelvariabel
som ska användas.
Exempel – Sortering av dataset
proc sort data = ab;
by IDNR;
run;
Exempel – Uppdatering med UPDATE
Huvuddatasetet LON innehåller alla anställdas
nuvarande lön med datum för senaste förändringen.
Vi har upptäckt att datumet för ANSTNR 6 är fel_
aktigt. Det ska vara 960304. Vi har även efter
löneförhandling höjt lönen för ANSTNR 10 som ska
erhålla 15000 med början 990601.
SAS-datset LON
Anstnr
Datum Lön
1
2
6
10
12
980202
990101
940304
980601
990101
16000
20000
16500
13500
14700
Ett dataset med de uppgifter som ska ändras skapas.
data lonupd;
input anstnr datum lon;
cards;
6
960304
10
990601
;
run;
SAS-datset LONUPD
.
15000
Idnr
Datum
6
10
960304 .
990601 15000
Klart för uppdatering:
data lon;
update lon lonupd;
by idnr;
run;
Detta går naturligtvis även att genomföra med
IF-satser, men prestandamässigt är detta det
snabbaste sättet. Märk även att endast alla giltiga
värden användes. Uppgifter som var missing
lämnades orörda.
Lön
SAS-datset LON
Anstnr
Datum Lön
1
2
6
10
12
15
16
17
980202
990101
960304
990601
990101
940603
960403
970101
16000
20000
16500
15000
14700
23200
14900
21250
Exempel – Nollställa uppgifter med UPDATE
Sidan 8
2009-01-20
Joins och Proc sql
Ibland kan det finnas ett behov av att nollställa uppgifter (sätta missing). För detta
ändamål finns ett dataset-options för UPDATE-statementet. Detta talar om vilket
uppdateringssätt man önskar och heter UPDATEMODE. Den kan ha två värden,
MISSINGCHECK eller NOMISSINGCHECK. Ursprungsvärdet (default) är MISSINGCHECK,
d.v.s. den kontrollerar så att värdet inte är missing innan uppdatering sker.
Detta händer om vi i det tidigare exemplet angivit UPDATEMODE =
NOMISSINGCHECK:
SAS-datset LON
data lon;
update lon lonupd UPDATEMODE = nomissingcheck;
by idnr;
run;
Sidan 9
Anstnr
Datum Lön
1
2
6
10
12
15
16
17
980202
990101
960304
990601
990101
940603
960403
970101
16000
20000
.
15000
14700
23200
14900
21250
2009-01-20
Joins och Proc sql
SET
Dataset-uttrycket SET används för att läsa observationer ifrån ett eller flera dataset.
Den enkla inläsningen har vi redan gått igenom.
Exempel “concatenating”:
Vi gick även igenom hur man kan länka ihop flera dataset i samma SET-sats (s.k.
concatenating = konkatenerat). I detta fall läses först datasetet GAMMAL1. När
läsning och skrivning från detta dataset är klar börjar läsning av dataset GAMMAL2.
Dataseten läggs “under” varandra.
data ny;
set gammal1 gammal2;
run;
Exempel “interleaving”:
Ett annat sätt som vi studerade var att länka ihop dataset med hjälp av en BY-sats.
På detta sätt blir slutprodukten ett sorterat dataset efter BY-satsen (s.k.
“interleaving” = sammanflätat).
data ny;
set gammal1 gammal2;
by idnr;
run;
Exempel – SET med (IN=) för att beräkna värde utifrån urprungsdataset
Vid länkningar genom en SET-sats kan dataset options (IN=) vara användbart,
framförallt om man ska göra olika beräkningar beroende på vilket dataset som
informationen kommer ifrån.
data sve;
set usa(in = usa) eu(in = eu);
if usa then belopp = belopp * 8.2371;
else if eu then belopp = belopp * 9.0040;
run;
Exempel – SET med två dataset och OUTPUT
Nästa steg i användningen av SET är att använda flera SET-satser i samma datasteg.
När man använder denna teknik gäller det att ha kontroll över var utskriften
(OUTPUT) till det nya datasetet sker. Om man exempelvis skriver följande …
data ny;
set a;
set a;
run;
… blir resultatet det samma som om man hade skrivet endast en SET-sats. Detta
beror på utskriften till det nya datasetet allt sker i slutet av datastegsloopen. Den
läser från två ställen men använder endast det sist lästa vid utskriften. Genom att
placera output efter varje SET-sats får man således dubbla dataset i NY.
Sidan 10
2009-01-20
Joins och Proc sql
Ett annat sätt är naturligtvis att endast använda en SET-sats med dubbla dataset
(SET a a)
data ny;
set a;output;
set a;output;
run;
Med hjälp av DO-satser kan man styra utskriften till sitt nya dataset fullständigt. Ett
användbar dataset option är [END = variabelnamn som “flaggar” för när man
kommer till sista observationen i ett dataset. Vi illustrerar genom detta exempel.
Exempel - Flera SET-satser med hjälp av DO-sats:
Vi vill skapa ett dataset som förenar dataset A och B. Vi vill gör alla beräkningar i
dataset A först innan beräkningar i dataset B görs.
data ny;
do while (not sista);
set A end = sista;
source = 'dataset A';
if idnr > 1500 then calc = (belopp * 1.10)/3.141592;
else calc = belopp / 3.141592;
output;
if sista then do;
idnr=.;calc=.;source='';
end;
end;
do while (not sistb);
set B end = sistb;
source = 'dataset B';
if idnr < 200 then calc = (belopp * 1.12324);
else calc = belopp;
output;
end;
run;
Genom detta förfarande läses först alla observationer i dataset A (samt beräkningar
görs) innan SAS går över till dataset B. Att tänka på är man i vissa lägen bör
nollställa variabler när sista observationen har bearbetats och skrivits ut till
datasetet.
Sidan 11
2009-01-20
Joins och Proc sql
Proc Append
En annan metod att lägga ihop två dataset är genom proceduren APPEND, som
lämpar sig framförallt när man ska lägga till en mindre fil till en stor basfil.
Proceduren kräver ganska mycket av informationen i de ingående dataseten.
Dataseten ska helst innehålla exakt lika många variabler med samma namn. I
normalläget fungerar inte proceduren om inte antalet variabler är lika i dataseten,
eller om variabellängd och typ är olika. För att komma förbi detta hårda krav finns
ett option FORCE som tvingar in tilläggsdatasetet till basdatasetet. Någon BY-sats
kan inte användas - tilläggsfilen läggs alltid i slutet av BASfilen.
I nedanstående exempel ska datasetet feb99 läggas till årsfilen. Eftersom en ny
variabel har tillkommit läggs FORCE-option in.
Exempel – lägga till data med APPEND:
proc append base = year99 data = feb99 FORCE;
run;
Sidan 12
2009-01-20
Joins och Proc sql
SAS/SQL
Proc SQL gör det möjligt att skriva Structured Query Language (SQL) i SAS. Med hjälp
av proc SQL kan du bland annat:
ƒ
Hämta och manipulera data lagrat i tabeller och vyer.
ƒ
Skapa tabeller, vyer och indexera kolumner i en tabell.
ƒ
Skapa macrovariabler med värden från en fråga.
ƒ
Lägga till eller ändra värden i en tabellkolumn.
ƒ
Lägga till eller ta bort en rad i en tabell.
ƒ
Skicka SQL-satser till andra databaser och hämta data från dessa.
ƒ
Skapa deskriptiv statistik som medeltal, antal, summeringar etc.
Proc SQL är en interaktiv procedur vilket betyder att den är aktiv tills den avslutas
med QUIT eller tills en annan procedur eller ett annat datasteg körs, till skillnad från
t.ex. ett dataseg, som avslutas endast med RUN.
Proceduren kan användas till många olika saker, bland annat att skapa tabeller eller
vyer genom att hämta information från andra tabeller, eller att skriva ut ett resultat
av en fråga till outputfönstret.
Hjälp och exempel om Proc SQL finns i hjälpen under SAS Product/Base SAS/SAS SQL
Procedure User’s Guide.
Exempel - resultat presenterat i output-fönstret
Ibland vill man bara ställa en enkel fråga och få ett svar utskrivet i outputfönstret,
alltså inte skapa en tabell som innehåller resultatet av frågan. Till exempel kanske
man vill ta reda på det senaste datumet som förekommer i en tabell. Syntaxen är
som följer:
proc sql;
select max(loaddate)
from tab1;
quit;
Det går att skapa riktigt snygga rapporter i output-fönstret med hjälp av Proc SQL,
men det är inget som kommer att beröras vidare här – se del 5 av denna kurs, som
behandlar rapporter.
Skapa tabeller och vyer
Ofta vill man spara resultatet av sin SQL-fråga. För att skapa en ny tabell eller vy
med Proc SQL anger du CREATE TABLE/VIEW samt namnet:
proc sql;
create table tab2 as
select col1, col2
from tab1;
quit;
Sidan 13
2009-01-20
Joins och Proc sql
Vill man att resultatet ska bli sorterat så lägger man till ORDER BY och anger sedan
kolumnen/kolumnerna som man vill sortera på:
proc sql;
create table tab2 as
select col1, col2
from tab1
order by col1;
quit;
Genom att ange ASC eller DESC efter kolumnerna kan man välja om resultatet ska
sorteras i stigande eller fallande ordning.
Calculated
Man kan skapa en ny variabel och därefter använda den i samma SELECT (eller
WHERE)-uttryck m.h.a. CALCULATED:
proc sql;
create table tab2 as
select col1
,col2 * 2 as new_col1
,col3 + calculated new_col1 as new_col2
from tab1;
quit;
Resultatet här blir alltså en tabell med tre variabler – col1, new_col1 och new_col2.
Notera också användandet av kolumnalias, skapas med AS.
Olika sätt att välja ut data
Exempel – WHERE:
WHERE går att använda som i ”vanliga” SAS:
proc sql;
create table tab2 as
select col1
,col2
from tab1
where col3 = 'A';
quit;
Exempel – IN():
Värt att notera att när man använder IN måste man ha kommatecken mellan
alternativen (vilket man inte behöver i ”vanliga” SAS):
proc sql;
create table tab2 as
select col1
,col2
from tab1
where col3 in ('A', 'B');
quit;
Exempel – LIKE:
Urval av rader där en av variablerna har en viss kombination av bokstäver i början
görs med LIKE kombinerat med ett %-tecken som wildcard:
Sidan 14
2009-01-20
Joins och Proc sql
proc sql;
create table tab2 as
select col1
,col2
from tab1
where col4 like 'AB%';
quit;
En annan operator som kan användas tillsammans med like är _. Den är wildcard för
en specifik position i ett ord, t.ex. He_ matchar Hej, Hel o.s.v., men inte Hejsan. Ett
exempel där man vill hitta alla observationer där fjärde bokstaven i variabeln col4 är
A:
proc sql;
create table tab2 as
select col1
,col2
from tab1
where col4 like '___A%';
quit;
CASE-satser
Motsvarigheten till SELECT-WHEN eller IF-THEN-ELSE i vanliga datasteg är CASEWHEN. Ett exempel där man skapar en ny variabel attribut utifrån en befintlig
variabel col2 (båda sparas i uttabellen):
proc sql;
create table tab2 as
select col1,
col2,
case col2
when 'BLÅ' then 'FÄRG'
when 'RÖD' then 'FÄRG'
when 'RUND' then 'FORM'
when 'KANTIG' then 'FORM'
else 'OKÄND'
end as attribut
from tab1;
quit;
Sidan 15
2009-01-20
Joins och Proc sql
Skapa tom tabell med rätt attribut
Om man vill skapa en tom tabell som har samma attribut som en existerande tabell
kan man använda LIKE:
proc sql;
create table tab2
like tab1;
quit;
INSERT INTO
Om man vill lägga till nya rader i en tabell kan det göras med INSERT INTO. Här
läggs en ny återförsäljare in i en styrtabell:
proc sql;
insert into tab1 (col1, col2)
values (101101, 'The Phone House');
quit;
Eller om man vill komplettera en tabell med delar av en annan tabell:
proc sql;
insert into tab1 (col1, col2)
select col1, col2
from tab2
where col1 = 1;
quit;
Ta bort rader från tabell
Syntaxen för att ta bort rader från en tabell med proc sql liknar syntaxen i vanliga
datasteg:
proc sql;
delete from tab1
where col1 = 1;
quit;
Eller om man vill ta bort alla rader i tabellen:
proc sql;
delete from tab1;
quit;
Observationer kan tas bort utan att index förstörs. Men det som händer när man gör
DELETE på en observation är att innehållet i observationen tas bort, inte själva
observationen. Observationen är liksom bara markerad som ”borttagen”. För att få
bort rader som markerats som borttagna så kan man göra en PROC COPY – då
rensas de här raderna bort.
Ta bort en hel tabell
Tabeller som ska tas bort helt ”droppas”:
proc sql;
Sidan 16
2009-01-20
Joins och Proc sql
drop table tab1;
quit;
Flera på samma gång:
proc sql;
drop table tab1, tab2;
quit;
Uppdatera en tabell
En tabell kan uppdateras på olika sätt m.h.a. ALTER, t.ex. att lägga till en kolumn:
Exempel – lägga till en kolumn
proc sql;
alter table tab1
add col5 char(3);
quit;
Exempel – ta bort en kolumn
proc sql;
alter table tab1
drop col1;
quit;
Exempel – modifiera en kolumn
Ibland kan man vilja ändra längden på en enstaka variabel. Om man gör detta i ett
vanligt datasteg så hamnar den ändrade variabeln först i tabellen vilket kanske inte
alltid är önskvärt. För att få variablerna i samma ordning som innan ändringen så får
man räkna upp variablerna i den ordning man vill ha dem. Då är det lite lättare med
Proc SQL:
proc sql;
alter table tab1
modify col3 char(5);
quit;
I det här fallet ändras inte ordningen på variablerna.
Sidan 17
2009-01-20
Joins och Proc sql
Subqueries
En subquery är ett sätt att välja ut vissa rader från en tabell m,h,a, en annan tabell, t
ex välja ut alla ordrar för kunder som ska betala med bankgiro från en styrtabell
över alla kunders betalningssätt:
proc sql;
create table bg_kunder as
select * from kundordrar
where kund_id in
(select kund_id from betalningssatt
where betsatt = 'BG');
quit;
SET-operatorer
Subsqueries kan också kombineras med s.k. SET-operatorer; UNION, EXCEPT,
INTERSECT och OUTER UNION – för att man ska kunna kombinera resultater från
frågorna på olika sätt.
UNION
Alla rader från båda frågorna.
EXCEPT
Rader som endast finns i den första frågan.
INTERSECT
Rader som finns i båda frågorna (gemensamma
rader)
OUTER UNION
Konkatenerar frågeresultaten
Exempel – Subquery kombinerat med SET-operatorn INTERSECT
Detta exempel skapar ett resultat med de rader som är gemensamma i båda
frågorna:
proc sql;
create table c as
select * from sql.a
intersect
select * from sql.b;
quit;
Sidan 18
2009-01-20
Joins och Proc sql
Skapa macrovariabler
Makrovariabler gör det möjligt att dynamiskt sätta ett värde, ändra text i ett SASprogram, eller kan användas för att dynamisk villkorsstyrning. Detta avsnitt
behandlar endast översiktligt hur man kan skapa makrovariabler i Proc SQL. För mer
information om makro och makrovariabler, se Del 4 av denna kurs.
Exempel – Skapa makrovariabel innehållande ett datum:
Om man skulle vilja ta ut det senaste datumet från en tabell och spara det i en
macrovariabel skriver man så här:
proc sql;
select max(loaddate) into :MAXDATE
from tab1;
quit;
%PUT &MAXDATE; * -- Skriver ut värdet på macrovariabeln i loggen -- ;
Exempel – Skapa makrovariabel med unika värden – SELECT DISTINCT:
Och på det här sättet skapar man en macrovariabel som innehåller alla unika värden
i en viss kolumn m.h.a. SELECT DISTINCT:
proc sql;
select distinct country into :PRESENT_COUNTRIES
from tab1;
quit;
Observera att en SELECT DISTINCT är en ”tung” fråga för en databas och kan därför
ta relativt lång tid att utföra. Detta bör man tänka på när man jobbar med stora
datamängder.
Exempel – Skapa makrovariabel med unika värden – SELECT DISTINCT:
I exemplet ovan hamnar alla värden i en enda lång sträng. Om man lättare vill kunna
identifiera de olika värdena använder man SEPARATED BY som avgränsar varje värde
i macrovariabeln:
proc sql;
select distinct country
into :PRESENT_COUNTRIES separated by '/'
from tab1;
quit;
Exempel – Skapa flera makrovariabler i samma steg:
Man kan också skapa flera macrovariabler i samma steg:
proc sql;
select count(distinct orgnr), max(loaddate)
into :UNIKAORG,
:MAXDATE
from tab1;
quit;
I det här exemplet har vi en styrtabell med tre rader (en per tabell) som talar om
vilka nyckelvariabler vi har för varje tabell:
proc sql;
Sidan 19
2009-01-20
Joins och Proc sql
select tabname, keyvar
into :TAB1
- :TAB3,
:KEYVAR1 - :KEYVAR3
from controltable;
quit;
Eller, om man vill att programmet ska fungera även om man lägger till fler rader i
styrtabellen, så här:
proc sql noprint;
select count(distinct tabname)
into :ANTAL
from controltable;
select tabname, keyvar
into :TAB1
- :TAB%left(&antal),
:KEYVAR1 - :KEYVAR%left(&antal)
from controltable;
quit;
Exempel – Skapa en makrovariabel som innehåller antal observationer:
Räkna antal observationer i en tabell kan man göra på många sätt, här är ett
exempel där man lägger resultatet i en macrovariabel:
proc sql noprint;
select count(*) into :N
from tab1;
quit;
NOPRINT medför att inget kommer att skrivas till outputfönstret. Den behövs inte
när man använder Proc SQL för att skapa en tabell.
Automatiska macrovariabler
Proc SQL skapar också egna macrovariabler. Nedan följer en lista på de automatiska
macrovariabler som alltid skapas:
SQLOBS
SQLRC
SQLOOPS
SQLXRC
SQLXMSG
Sidan 20
Antal rader som exekverades av sql-satsen.
Innehåller returvärden från proc sql, se i hjälpen vilka värden den
antar och vad de betyder.
Antalet loopar som proc sql exekverar.
DBMS-specifika returkoder från Pass-Through.
DBMS-specifika returkoder för fel returnerade från Pass-Through.
2009-01-20
Joins och Proc sql
Sammanslagning av tabeller – join
I stället för att använda datasteg och MERGE för att slå ihop två eller flera tabeller
kan man använda sig av join i Proc SQL. När man använder join får man fler
möjligheter att styra vilket resultat man kommer att få av sammanslagningen.
I exemplen nedan används följande tabeller:
Namn
Id
1
2
3
4
Lön
Id
1
3
4
5
Namn
Nisse
Pelle
Stina
Anna
Lön
12000
10000
5000
13000
Inner join
En inner join returnerar en tabell som innehåller alla rader i den ena tabeller som
matchar en eller flera rader i den andra tabellen. I en inner join kan det ingå upp till
16 tabeller.
Gör vi en inner join mellan de två tabellerna ovan skulle vi få följande resultat:
Inner join
Id
Namn
1
Nisse
3
Stina
4
Anna
Lön
12000
10000
5000
För att göra joinen ovan finns det två sätt att skriva:
proc sql;
create table
select n.id
,namn
,lon
from namn as
,lon as
where n.id =
quit;
ijoin as
n
l
l.id;
Samma resultat får man med följande kod:
proc sql;
create table ijoin as
select n.id
,namn
,lon
from namn as n
inner join lon as l
on n.id = l.id;
quit;
Sidan 21
2009-01-20
Joins och Proc sql
I exemplen ovan används ALIAS för tabellernas namn för att öka läsbarheten. Utan
alias skulle den senaste joinen se ut så här:
proc sql;
create table ijoin as
select namn.id
,namn
,lon
from namn
inner join lon
on nanm.id = lon.id;
quit;
Visserligen helt klart läsligt, men med längre variabelnamn och fler variabler
inblandade i joinen så blir det ganska snart betydligt snårigare.
Outer join
En outer join returnerar dels samma resultat som en inner join men dessutom
returneras också de rader i den ena tabellen som inte matchar i den andra tabellen. I
en outer join kan man bara använda två tabeller. Det finns tre olika typer av outer
join: LEFT JOIN, RIGHT JOIN och FULL JOIN. Använder man LEFT JOIN är det alla
värden i den vänstra tabellen som kommer med i resultattabellen oavsett om de
matchar med någon av raderna i den högra tabellen. På motsvarande sätt fungerar
RIGHT JOIN. En FULL JOIN gör att man får med samtliga rader från båda tabellerna.
Exempel - left outer join
Använder vi samma tabeller som i det förra exemplet och gör en LEFT OUTER JOIN
med tabellen NAMN som ”vänstertabell” får vi följande resultat:
Left outer join
Id
Namn
1
Nisse
2
Pelle
3
Stina
4
Anna
Lön
12000
.
10000
5000
Vi får nu med alla rader som fanns i den vänstra tabellen NAMN, men Pelle finns inte
med i tabellen LÖN så därför får variabeln Lön värdet missing för Pelle i
resultattabellen. Och så här kan man skriva:
proc sql;
create table ljoin as
select n.id
,namn
,lon
from namn as n
left join lon as l
on n.id = l.id;
quit;
Exempel - right outer join
En RIGHT OUTER JOIN används för att få med alla rader från den andra tabellen i
SQL-satsen (givetvis skulle man lika gärna kunna ändra ordningen på tabellerna).
proc sql;
Sidan 22
2009-01-20
Joins och Proc sql
create table rjoin as
select l.id
,namn
,lon
from namn as n
right join lon as l
on n.id = l.id;
quit;
Exempel - full outer join
Använder man en en FULL OUTER JOIN får man med alla raderna från båda
tabellerna.
proc sql;
select * from
namn as n
full join lon as l
on n.id = l.id;
quit;
I exemplet ovan skrivs resultatet till outputfönstret. Vill man istället spara resultatet
som en ny tabell måste man explicit ange de båda matchningsvariablerna i SELECTsatsen, så här:
select n.id as namn_id, l.id as lon_id, ... from
Cartesian product
En kartesisk produkt blir resultatet när man slår samman två tabeller där båda
tabellerna har dubletter m.a.p. join-variablerna, man kan kalla det en ”många till
många”-join kanske. Resultatet blir en stor tabell som innehåller alla möjliga
kombinationer av värdena som förekommer i de två tabellerna som slås ihop, vilket
oftast inte är önskvärt.
Summeringar
I SELECT-satsen kan man använda en mängd olika funktioner som summa,
medelvärde, max, min osv. Dessa anges i SELECT-satsen och bildar nya kolumner.
Vill man göra en beräkning för en viss gruppering i materialet (jämför med
CLASS/BY i Proc SUMMARY/MEANS) använder man i Proc sql GROUP BY.
Sidan 23
2009-01-20
Joins och Proc sql
Exempel – Summering och MAX/MIN, grupperat
Vi vill räkna ut innehavet i antal per idnummer (person) i datasetet TESTIHAV,
dessutom vill vi se MAX och MIN.
proc sql;
select idnr,
sum(antal) as summa,
max(antal) as max ,
min(antal) as min
from kurs.testihav
group by idnr;
quit;
Index
Med hjälp av Proc SQL är det enkelt att skapa enkla eller sammansatta index för ett
dataset. Gör man ett enkelt index på bara en kolumn måste indexnamnet vara
samma som namnet på kolumnen, innehåller indexet däremot flera kolumner får
indexnamnet inte vara samma som något av kolumnnamnen. Index i SAS uppdateras
om det görs någon förändring i datasetet med Proc SQL.
proc sql;
create <UNIQUE> INDEX index-name
quit;
ON table-name (column
<,column>...);
Genom att ange nyckelordet UNIQUE kan man säkerställa att en viss kolumn alltid
har unika värden.
Exempel - Skapa och ta bort ett index:
I detta exempel skapas ett index på organisationsnummer i datasetet COMPANY.
proc sql;
create index orgnr on company(orgnr);
quit;
Index tas bort så här:
proc sql;
drop index idx1
from tab1;
quit;
SQL passthru
Vissa variabelnamn är skyddade och kan orsaka fel, s.k. SQL passthru error.
Dessa visas med en liten brädgård # framför variabelnamnet i loggen. Detta
kan undvikas genom att sätta \ framför variabelnamnet.
Länkar och litteraturtips
Det finns en hel del matnyttig dokumentation kring Proc SQL i online-hjälpen
för SAS. Online-hjälpen har i skrivande stund följande länk:
http://support.sas.com/onlinedoc/913/docMainpage.jsp
Tips på en bok som beskriver grunderna på ett bra sätt men också innehåller tips
som kan vara användbara för den något mer erfarne Proc SQL-användaren:
Sidan 24
2009-01-20
Joins och Proc sql
PROC SQL – ”Beyond the Basics Using SAS” av Kirk Paul Lafler
Sidan 25
2009-01-20
Joins och Proc sql
Att använda SAS metainformation
SAS lägger automatiskt upp en hel del information OM den information som finns
tillgänglig i den aktuella SAS-sessionen, d.v.s. metainformation. Exempel på sådan
information är:
ƒ
ƒ
ƒ
Vilka FILE/LIBNAMEs som finns definierade och vad har de för fysisk sökväg
Vilka dataset finns i ett visst LIB och vilka variabler dessa innehåller
Hur SAS-options är satta
Informationen finns upplagd som vyer i LIB SASHELP. Namnen på dessa vyer börjar
med V…, t.ex. SASHELP.VMEMBER som innehåller information (namn, typ etc.) om
medlemmar i alla libraries som finns uppdefinierade. De vyer som finns är:
VCATALG
VCOLUMN
VEXTFL
VINDEX
VMACRO
VMEMBER
VOPTION
VSACCES
VSCATLG
VSLIB
VSTABLE
VSTABVW
VSVIEW
VTABLE
VTITLE
VVIEW
Dessa vyer läses precis som vanliga dataset.
Sidan 26
2009-01-20
Joins och Proc sql
Det finns ett alternativt sätt att läsa in metadata som är mer effektivt än att läsa
ovanstående vyer i datasteg. I Proc SQL, dvs. SAS miljö för SQL-satser, finns ett antal
dictionaries upplagda. Dessa refereras genom att använda LIBNAME ’dictionary’.
T.ex. VMEMBER motsvaras här av tabellen ’dictionary.tables’. Det går alltid betydligt
snabbare att läsa denna information i ett SQL-steg än att läsa SASHELP-vyerna i ett
datasteg. Dock finns inte all information tillgänglig här. De tabeller som finns är:
CATALOGS
COLUMNS
EXTFILES
INDEXES
MACROS
MEMBERS
OPTIONS
TABLES
TITLES
VIEWS
Exempel - Ta reda på vilka dataset i ett bibliotek som innehåller en viss variabel:
I detta exempel ska vi ta reda på vilka dataset i testlib som innehåller variabeln x1:
Några dataset skapas i TESTLIB:
data testlib.a testlib.b testlib.c(rename=(x1=x2));
x1='xxx';
run;
Dataseten a och b innehåller därmed variabeln x1 och det är dessa datasetnamn
som ska tas fram med metoderna nedan.
Datasteg:
data utdata;
set sashelp.vcolumn(where=(libname='TESTLIB' and name='X1')
keep=libname memname name);
run;
Sqlsteg:
proc sql;
create table utdata as select memname from dictionary.columns
where libname='TESTLIB' and name='X1';
quit;
Kommentar: Metoderna ovan ger samma resultat, men datasteget tog vid test över
30 (!!) sekunder medan sql-satsen tar 0,27 sekunder!! Skillnaden är extrem vid just
metainformation om variabler från column/columns, men sql går alltid betydligt
snabbare när man ska läsa metainformation från SAS. Detta är viktigt att ha i
bakhuvudet när man vill använda SAS metainformation!
Sidan 27
2009-01-20