PROC SQLの紹介#
PROC SQLはデータ操作とクエリ(問い合わせ)を行う強力なツールです。従来のデータステップやPROCステップと同様の機能を、より少ないステートメントとコンピュータリソースで実行できます。このレッスンでは、SQLプロシージャを使用してデータを選択、サブセット、ソート、要約、グループ化する方法を扱います。
PROC SQLの基本#
PROC SQLは、SASがSQL(Structured Query Language)を実装するために開発したプロシージャです。このプロシージャを使用して、テーブルやビュー（テーブルを元に作成された）内のデータを修正、取得、レポートできます。他のSASプロシージャと同様に、PROC SQLにも基本的な構文があり、以下が一般的な構文です：
PROC SQL;
SELECT column-1<,…column-n>
FROM table-1|view-1<,…table-n|view-n>
<WHERE expression>
<GROUP BY column-1<,…column-n>>
<HAVING expression >
<ORDER BY column-1<,…column-n >>;
QUIT;
まず最初に、SQLとSASのデータステップとの間の用語の違いに注意してください。例えば、データファイルはデータステップではデータセットと呼ばれますが、SQLではテーブルと呼ばれます。同様に、レコードは前のレッスンではオブザベーションと呼ばれましたが、SQLテーブルでは行と呼ばれます。そして、データセットのフィールドは変数と呼ばれますが、ここでは列と呼ばれます。
SASのステップ |
SQL プロシージャ |
|---|---|
data set |
table |
observation |
row |
variable |
column |
もう一つ注意すべき点は、他のプロシージャとは異なり、PROC SQLの中には1つまたは複数のSELECTステートメントが存在する可能性があるということです。1つのSELECTステートメントはクエリと呼ばれ、多くの句（SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY）で構成されます。これらの句の順序は重要であり、上記の順序で表示される必要があります。
ここでは全体を通して、PROC SQLのすべてのキーワードを順に学んでいきます。まずは最も基本的なものから始めましょう。
例#
以下のSASによるSQLコードは、単一のテーブルからデータを取得する単純なクエリです：
libname phc6089 "/folders/myfolders/SAS_Notes/data";
proc sql;
select ID, SATM, SATV
from phc6089.survey;
quit;
上記のプログラムを実行するには、まずデータファイル（survey.sas7bdat）をPCに保存する必要があります（READMEを参照）。LIBNAMEステートメントを編集して、調査データセットを保存したディレクトリを反映させます。その後、プログラムを実行して出力を確認します。このコードは、プロシージャの最も基本的な形式を表しています。他のSASプロシージャと同様に、PROC SQLを実行するには最初にPROC SQLを記述する必要があります。プロシージャ内には、選択したい列を指定するSELECTステートメントが1つあります。必要な列をカンマで区切ってリストすることができます。もう1つの句はFROMで、テーブルを指定するために使用されます。PROC SQLはSASファイル名のプロトコルに従います。ここでは、永続的なファイルを参照するために2レベル名を使用しています。このプログラムは、テーブルから3つの列（学生ID、SAT数学スコア、SAS言語スコア）を選択するためのものです。
例#
次のプログラムはCREATE TABLEステートメントを使用して、学生ID、SAT数学スコア、および言語スコアを含む新しいテーブルSAT_scoresを作成します。
proc sql;
create table SAT_Scores as
select ID, SATM, SATV
from phc6089.survey;
quit;
プログラムを開いて実行します。出力ウィンドウや開いているODSの出力先には何も表示されません。これはCREATE TABLEステートメントがクエリの出力を抑制するためです。しかし、ログウィンドウを確認すると、テーブルが作成されたことと、テーブルの行数と列数が表示されます。この例では、テーブルSAT_scoresには226行と3列があります。新しいテーブルの列は、選択されたソース列と同じ属性（型、長さ、フォーマット、ラベル）を持ちます。
これまでの2つの例から、PROC SQLを使用する感覚がつかめたと思います。次に、他のSASプロシージャとは何が違うのかをまとめます。
他の多くのステートメントを含むSASプロシージャとは異なり、SQLプロシージャには1つまたは複数のSELECTステートメントが含まれる場合があります。各SELECTステートメントには、SELECT、FROM、WHERE、ORDER BYなどのいくつかの句が含まれます。ただし、SELECTとFROM句は必須であり、省略できません。他の句はオプションです。すべての句は、構文でリストされた順序で記述する必要があります。各SELECTステートメントには、ステートメントの最後にセミコロンが1つ必要です。
PROC SQLを実行するためにはRUNステートメントは必要ありません。SQLプロシージャはプログラムが実行され続けます。終了するには、別のPROCステップ、データステップ、またはQUITステートメント実行する必要があります。
SELECT句の使用#
前のセクションでは、PROC SQLの基本を扱いました。次に、SELECT文の詳細と、それを使用してデータを取得し、新しい列を作成し、データ操作のために利用できるオプションについて確認します。
例#
以下のプログラムは、データファイルtraffic.sas7bdat（READMEを参照）からすべての列を取得し、新しい一時テーブルを作成します：
proc sql;
create table traffic as
select *
from phc6089.traffic;
quit;
proc contents data=traffic varnum;
run;
proc contents data=phc6089.traffic varnum;
run;
まず、データセットのファイルtrafficを自分のPCにダウンロードする必要があります。必要に応じてlibnameステートメントを修正します。その後、プログラムを実行します。このプログラムについて知っておくべきことは、SELECTの後の省略記号のアスタリスク（*）です。アスタリスクは元のテーブル内のすべての列を指します。したがって、このコードは元のテーブルのすべての列を一時データセットtrafficに選択するものです。
データを確認するには、これまでに扱った他のプロシージャ（PRINTプロシージャなど）を使用できます。上記のプログラムでは、PROC CONTENTSが使用されて元のテーブルと新しいテーブルの変数の属性を確認します。前のセクションで述べたように、他のテーブルから選択された変数は同じ属性を保持します。
元の列を選択する以外に、SELECT句を使用して新しい列を作成することもできます。これは、データステップで新しい変数を作成するために割り当てステートメントを使用したのと同様です。
例#
次のプログラムは、SELECT句を使用して新しい列を作成します：
proc sql;
select id, count_location,
scan(count_location,-1,' ') as orientation,
street,
passing_vehicle_volume * 0.5 as weekends_traffic_volume
from traffic;
quit;
ご覧の通り、このコードは前に作成したテーブルtrafficを使用しています。SELECT文を使用して、文字列や数値を含む新しい列を作成できます。SELECT句内で有効なステートメントを使用して、新しい列に対して任意の式を使用できます。また、新しい列には、キーワードASの後に使用したい名前を付けることができます（列名もSAS名のルールに従います）。上記のコードでは、最初の新しい列は文字関数scan()を使用して既存の列「count_location」から部分文字列を抽出して作成されます。この新しい列の名前はASの後の「orientation」です（例のための名前で意味はありません）。2番目の新しい列は、日々の車両数に0.5を掛けることで週末の交通量を推定する数式で、新しい列名は「weekend_traffic_volume」です。
プログラムを開いて実行し、出力から期待通りに2つの新しい列が作成されたことを確認してください。ただし、新しい列は実行(問い合わせ・クエリ)の間だけ存在し、テーブルを作成しない限り永続的にはなりません。
データtrafficを見てみると、一部のデータが望ましい形式になっていないことに気づくかもしれません。幸いなことに、SELECTステートメント内でデータの見た目を向上させるための多くのオプションが提供されています。
例#
次のプログラムは、日付のフォーマットを追加し、列にラベルを付け、出力にタイトルを追加します：
title "Traffic volume in Area SS";
title2 "During weekdays and weekends";
proc sql;
select id,
Date_of_count label='Date of Count' format=mmddyy10.,
count_location label='Location',
street,
passing_vehicle_volume label='Daily Volume' format=comma6.,
passing_vehicle_volume * 0.5 as weekends_traffic_volume label='Weekends Volume' format=comma6.
from traffic;
quit;
プログラムを開いて実行し、結果の出力からデータが期待通りにフォーマットされ、ラベルが付けられていることを確認してください。タイトルのほかに、脚注をfootnoteステートメントを使用して出力に追加することもできますが、他のSASステップとタイトルやフットノートステートメントを使用する場合とは異なり、これらのステートメントはPROC SQLステートメントの前、またはPROC SQLステートメントとSELECT文の間に配置する必要があります。
このセクションで最後に説明するのは、CASE式です。これは、SELECT句の直後に続けて新しい列を条件付きで作成するために使用されます。これはデータステップで使用されるIF-THEN-ELSEステートメントと同等の機能を果たします。まず、CASE式の構文を見てみましょう。
CASE
WHEN when-condition THEN result-expression
<… WHEN when-condition THEN result-expression>
<ELSE result-expression>
END AS < column name>
IF-THENステートメントと同様に、WHEN条件をいくつでも追加できます。条件は計算、関数、論理演算子など、任意の有効なSAS式を使用できます。条件が満たされた場合、キーワードTHENに続く対応するアクションが実行されます。WHEN条件が偽の場合、PROC SQLはELSE式を実行します。新しい列を作成し、ENDの後にASキーワードを使用して名前を付けることができます。ELSEとASキーワードはオプションですが、新しい列を作成する際には元の列を保持しておくのが良いとされています。
例#
次のプログラムは、給与の各範囲に対して異なる給与引き上げ計画を割り当てるためにCASE式を使用します：
proc sql ;
select Name,
Department,
employee_annual_salary label='salary' format=dollar12.2,
'next year raise:',
case
when employee_annual_salary=. then .
when employee_annual_salary < 85000 then 0.05
when 85000 <= employee_annual_salary < 125000 then 0.03
when employee_annual_salary >=125000 then 0.01
else 0
end as raise format=percent8.
from phc6089.salary;
quit;
前の説明でフォーマットとラベルのオプションはすでに扱っています。しかし、この例ではいくつかの新しい点があります。まず、テーブルに新しい列として文字列（または数値）定数を挿入できます。ここでは、文字列”next year raise”が「employee_annual_」と「raise」の間に追加されています。「raise」はCASE式によって現在の年収に基づいて作成された新しい列です。 データセットsalary.sas7bdatをPCにダウンロードし、libnameステートメントをファイルを保存したディレクトリに合わせて修正します。その後、プログラムを開いて実行します。結果から引き上げ値が正しく割り当てられたことを確認してください。
CASE式には2つの構文があります。1つの列のみをWHEN条件に使用する場合、この列名をWHENの前にCASEの後に置くことができます。これにより、各WHEN条件で列名を繰り返す必要がなくなります。以下はこの形式の構文です：
CASE <column-name>
WHEN when-condition THEN result-expression
<… WHEN when-condition THEN result-expression>
<ELSE result-expression>
END AS < column name>
例#
次のプログラムは、部門に基づいて補償（YesまたはN/A）を決定するために、より簡単な形式のCASE構文を使用します：
proc sql outobs=20;
select name,
Department,
employee_annual_salary label='salary' format=dollar12.2,
case department
when 'POLICE' then 'Yes'
when 'FIRE' then 'Yes'
else 'N/A'
end as Compensation
from phc6089.salary;
quit;
| Name | Department | salary | Compensation |
|---|---|---|---|
| WARNER | PROCUREMENT | $76,980.00 | N/A |
| EDWARDS | POLICE | $83,616.00 | Yes |
| PENDARVIS | POLICE | $103,590.00 | Yes |
| CLARK | FIRE | $85,680.00 | Yes |
| FOLINO | POLICE | $83,616.00 | Yes |
| SAWYER | CITY COUNCIL | $117,333.00 | N/A |
| WALTON | POLICE | $89,718.00 | Yes |
| QUINLAN | TRANSPORTN | $95,888.04 | N/A |
| SOTO | WATER MGMNT | $79,040.00 | N/A |
| RUSS | WATER MGMNT | $79,040.00 | N/A |
| MC GUIRE | POLICE | $86,520.00 | Yes |
| GAWRISCH | POLICE | $86,520.00 | Yes |
| CANO | FIRE | $86,520.00 | Yes |
| CLARK | STREETS & SAN | $72,862.40 | N/A |
| GIBOWICZ | POLICE | $83,616.00 | Yes |
| MANCILLA | POLICE | $90,456.00 | Yes |
| WYATT | WATER MGMNT | $65,686.40 | N/A |
| VALLE | FIRE | $54,114.00 | Yes |
| SISSAC | PUBLIC LIBRARY | $12,407.20 | N/A |
| ARMSTEAD | POLICE | $80,778.00 | Yes |
上記のコードは、前の例と同じデータセットsalaryを使用しています。ここでは、所属する部門に基づいて異なる補償プランを割り当て、新しい列「Compensation」を結果として作成します。今回は、部門名がWHEN条件の外に出され、CASE式に入れられています。そのため、「WHEN department=’POLICE’」のようなコーディングは必要ありません。
もう一つの特徴は、PROC SQLステートメントで使用できるオプション、OUTOBS=nです。これは、出力に表示される行数を制限するために使用できます。この場合、出力ウィンドウにデータの最初の20行が表示されることが期待されます。また、ログウィンドウには次のような警告メッセージが表示されます：
WARNING: ステートメントは、OUTOBS=20オプションにより早期終了しました。
OUTOBS=はCREATE文で作成されたテーブルにも影響を与えることに注意してください。
プログラムを開いて実行し、レコードが期待通りに処理されたことを確認します。この簡単な形式を使用する場合には注意が必要です。例えば、前の例のプログラムで「Employee_annual_salary」をWHEN条件の外に移動すると、エラーとなり実行されません！
WHERE句の使用#
ご存知のように、データステップや他のプロシージャでのWHEREステートメントやオプションは、指定された条件に基づいてデータセットからオブザベーションを選択するのに非常に役立ちます。PROC SQLでは、SELECT文でWHERE句を使用して、指定された条件に基づいてデータをサブセット化することもできます。WHERE句内には、SAS式を入れることができ、関数、比較演算子、論理演算子、さらには特別な演算子を含むことができます。これをうまく活用することで、プログラミングの効率が向上し、計算資源を大幅に節約できます。いつものように、例を使ってこのテーマを進めていきます。
例#
次の例ではWHERE句を使用して、部門が”POLICE”で職位が”SERGEANT”である従業員を選択します：
proc sql;
select Name, Department, Employee_annual_salary
from phc6089.salary
where Department='POLICE' AND Position_title='SERGEANT';
quit;
| Name | Department | Employee Annual Salary |
|---|---|---|
| PENDARVIS | POLICE | 103590 |
| ODUM | POLICE | 106920 |
| DANIELS | POLICE | 106920 |
| TATE JR | POLICE | 103590 |
| GOLDSMITH | POLICE | 110370 |
| FRANKO | POLICE | 106920 |
| RADDATZ | POLICE | 110370 |
| FLEMING | POLICE | 100440 |
| CASEY | POLICE | 110370 |
| MC COY | POLICE | 110370 |
| JACOBS | POLICE | 106920 |
プログラムを読み進めると、給与データから’POLICE’の’SERGEANT’の名前、部門、および年収情報を選択していることがわかります。WHERE句内の列はSELECT句に指定する必要はありません（例えば、Position titleのように、WHERE句で使用されていますが、SELECT句には含まれていません）。しかし、結果を確認するために、これらの列をクエリに含めておくことをおすすめします。
プログラムを開いて実行し、出力からレコードが説明通りに選択されていることを確認してください。 上記のプログラムでは、比較演算子（=）と論理演算子（AND）の2種類の演算子が使用されています。これらの一般的なものに加えて、プログラミングに非常に有用な別のタイプの演算子があります。これを条件演算子と呼びます。IN、CONTAINS、およびMISSINGなどの演算子を既にご存知かもしれません。完全な演算子のリストはSASドキュメントにあります。次に、BETWEEN ANDおよびLIKEの使用例を見てみましょう。
BETWEEN value-1 AND value-2
value-1とvalue-2の両方が終了値です。そのため、BETWEEN AND演算子を使用して、ある日付から別の日付まで、または下限から上限までの値の範囲を指定できます。小さい値が最初である必要はありません。
例#
次のプログラムは、BETWEEN AND演算子を使用して、年収が65,000ドルから70,000ドルの間であり、かつ部門’FIRE’で働く従業員のオブザベーションを選択します：
proc sql;
select Name, Department,
Employee_annual_salary label='Salary' format=dollar12.2
from phc6089.salary
where Employee_annual_salary between 65000 and 70000
and Department='FIRE';
quit;
| Name | Department | Salary |
|---|---|---|
| KELLY | FIRE | $65,946.00 |
| KOCHANEY | FIRE | $65,946.00 |
プログラムを開いて実行し、クエリの出力から期待通りの結果が得られていることを確認してください。
もう一つの有用な演算子はLIKE演算子です：
Column LIKE ‘pattern’
LIKE演算子を使用する場合、列名と一致するパターンを指定する必要があります。パターンに関しては、まず大文字と小文字が区別され、引用符で囲む必要があります。次に、アンダースコア(_)やパーセント記号(%)などの特殊文字を含むことができます。アンダースコア文字は任意の単一文字を表し、パーセント記号は0文字以上の任意の文字列を表します。例えば、ある列に次の値が含まれているテーブルを扱っているとします。
Cathy
Kathy
Kathie
Katherine
異なるパターンを使用すると、選択結果も異なります。
パターン |
結果 |
|---|---|
Kath_ |
Kathy |
Kath__ |
Kathie |
Kath% |
Kathy, Kathie, Katherine |
_ath% |
All of the names above |
例#
次のプログラムは、WHERE句でLIKE演算子を使用して、名前がRで始まり、3文字目がBである人の名前、部門、職位、および年収情報を選択します：
proc sql;
select Name, Department, Position_title,
Employee_annual_salary label='Salary' format=dollar12.2
from phc6089.salary
where Name like 'R_B%';
quit;
| Name | Department | Position Title | Salary |
|---|---|---|---|
| ROBINSON | WATER MGMNT | OPERATING ENGINEER-GROUP C | $93,745.60 |
| RABANALES | FINANCE | AUDITOR II | $87,912.00 |
| ROBERTS | FIRE | PARAMEDIC I/C | $79,404.00 |
| ROBINSON | WATER MGMNT | CONSTRUCTION LABORER | $79,040.00 |
プログラムを開いて実行し、クエリの出力から説明通りに動作していることを確認してください。
ここで注目すべきもう一つのポイントはキーワードCALCULATEDです。前のセクションで、SELECTステートメントで計算を行い、新しい列にエイリアスを割り当てることができることを学びました。しかし、SASがWHERE句をSELECT句の前に処理するため、計算された列をWHERE句で条件として使用すると問題が発生します。したがって、CALCULATEDキーワードをWHERE句にエイリアスとともに挿入して、値がクエリ内で計算されたことを通知する必要があります。この点については次のプログラムで説明します。
例#
次のプログラムは、各従業員のボーナスを計算し、ボーナスが2000ドルを超えるものを選択します：
proc sql;
select Name, Department,
Employee_annual_salary label='Salary' format=dollar12.2,
Employee_annual_salary * 0.02 as Bonus
from phc6089.salary
where Bonus > 2000 ;
quit;
17 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
126 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
126 ! ods graphics on / outputfmt=png;
127
128 options notes ;
129 proc sql;
130 select Name, Department,
131 Employee_annual_salary label='Salary' format=dollar12.2,
132 Employee_annual_salary * 0.02 as Bonus
133 from phc6089.salary
134 where Bonus > 2000 ;
NOTE: データファイルPHC6089.SALARY.DATAは別なホストにネイティブな形式が使用されているか、または
エンコーディングがセッションエンコーディングと一致していません。クロス環境データアク
セスが使用されるため、追加のCPUリソースが必要となり、パフォーマンスが低下します。
ERROR: 次の列はテーブル内に存在しません: Bonus.
NOTE: PROC SQLはNOEXECオプションを設定し、ステートメントの構文をチェックします。
135 quit;
NOTE: エラーが発生したため、このステップの処理を中止しました。
NOTE: PROCEDURE SQL処理(合計処理時間):
処理時間 0.00 秒
ユーザーCPU時間 0.00 秒
システムCPU時間 0.00 秒
メモリ 5598.71k
OSメモリ 24224.00k
タイムスタンプ 2024/06/06 午前08:49:19
ステップ数 8 スイッチ数 0
ページフォルト回数 0
ページリクレーム回数 84
ページスワップ回数 0
自発的コンテキストスイッチ回数 9
非自発的コンテキストスイッチ回数 0
ブロック入力操作回数 0
ブロック出力操作回数 0
136
137
138 ods html5 (id=saspy_internal) close;ods listing;
139
18 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
140
ERROR: 次の列はテーブル内に存在しません: Bonus. None
プログラムを開いて実行します。自分で何が問題なのかを確認したいかもしれません。
ログウィンドウには、列「Bonus」が見つからないというエラーメッセージを表示します（上記の出力参照）。これは、WHERE句をSELECT句の前に処理されるためです。これを正しくするためには、WHERE句にCALCULATEDを以下のように追加します。
proc sql;
select Name, Department,
Employee_annual_salary label='Salary' format=dollar12.2,
Employee_annual_salary * 0.02 as Bonus
from phc6089.salary
where calculated Bonus > 2000 ;
quit;
| Name | Department | Salary | Bonus |
|---|---|---|---|
| PENDARVIS | POLICE | $103,590.00 | 2071.8 |
| SAWYER | CITY COUNCIL | $117,333.00 | 2346.66 |
| ODUM | POLICE | $106,920.00 | 2138.4 |
| PLANTZ | GENERAL SERVICES | $102,060.00 | 2041.2 |
| FORD | FIRE | $100,440.00 | 2008.8 |
| DANIELS | POLICE | $106,920.00 | 2138.4 |
| MALONEY | FIRE | $127,566.00 | 2551.32 |
| MOLLOY | COMMUNITY DEVELOPMENT | $102,060.00 | 2041.2 |
| NIEGO | FIRE | $143,682.00 | 2873.64 |
| PRICE | FIRE | $103,590.00 | 2071.8 |
| TATE JR | POLICE | $103,590.00 | 2071.8 |
| JIMENEZ | PROCUREMENT | $113,448.00 | 2268.96 |
| CEBALLOS | WATER MGMNT | $113,448.00 | 2268.96 |
| FERMAN | FIRE | $131,466.00 | 2629.32 |
| SHUM | TRANSPORTN | $104,736.00 | 2094.72 |
| FUNK | FIRE | $101,688.00 | 2033.76 |
| WRZESINSKI | FIRE | $105,918.00 | 2118.36 |
| GOLDSMITH | POLICE | $110,370.00 | 2207.4 |
| FRANKO | POLICE | $106,920.00 | 2138.4 |
| RADDATZ | POLICE | $110,370.00 | 2207.4 |
| MC NABB | FIRE | $105,918.00 | 2118.36 |
| FLEMING | POLICE | $100,440.00 | 2008.8 |
| CASEY | POLICE | $110,370.00 | 2207.4 |
| MACELLAIO JR | WATER MGMNT | $101,920.00 | 2038.4 |
| DARLING | LAW | $149,160.00 | 2983.2 |
| NASH | FINANCE | $103,740.00 | 2074.8 |
| MC COY | POLICE | $110,370.00 | 2207.4 |
| HOLDER | WATER MGMNT | $108,534.40 | 2170.688 |
| TAYLOR | FIRE | $108,462.00 | 2169.24 |
| PERFETTI | POLICE | $112,068.00 | 2241.36 |
| JACOBS | POLICE | $106,920.00 | 2138.4 |
| HENRY | AVIATION | $108,534.40 | 2170.688 |
| IRELAND | FIRE | $113,400.00 | 2268 |
これで動くようになりました! 自分のプログラムでも同様に修正し、正しく動作しているか確認してください。
キーワードCALCULATEDを使う以外の方法は、WHERE句で再度計算式を書くことです。下のプログラムではWHERE句が書き換えられています:
where Employee_annual_salary *0.02 >2000;
しかし、この場合2回計算を行う必要があるため、あまり効率的でないことに注意してください。
データのソート#
SELECT句とFROM句は、SQLクエリにおいて欠かせないものです。他の句はオプションですが、テーブルに問い合わせをする際に非常に役立ちます。前のセクションではWHERE句による条件付きで行を選択する方法を紹介しました。これまでの例から、オブザベーションの順序が元のデータセットと同じままであることに気付いたかもしれません。しかし、データの順序を指定したい場合は、ORDER BY句を使用してデータを希望の順序にソートする必要があります。
例#
以下のプログラムは、PROC SQL内でORDER BYを使用して、ファイルsurvey.sas7bdatのデータを性別とGPAの値でソートします：
proc sql;
select ID, Gender, GPA, SATM, SATV
from phc6089.survey
where SATV is not null and GPA > 3
order by Gender, GPA ;
quit;
| id | Gender | GPA | SATM | SATV |
|---|---|---|---|---|
| 1219 | Female | 3.01 | 630 | 590 |
| 1039 | Female | 3.02 | 560 | 560 |
| 1125 | Female | 3.08 | 470 | 510 |
| 1203 | Female | 3.1 | 500 | 610 |
| 1139 | Female | 3.1 | 530 | 600 |
| 1068 | Female | 3.1 | 550 | 610 |
| 1116 | Female | 3.1 | 560 | 550 |
| 1072 | Female | 3.1 | 570 | 570 |
| 1138 | Female | 3.12 | 560 | 580 |
| 1120 | Female | 3.16 | 680 | 670 |
| 1102 | Female | 3.2 | 620 | 630 |
| 1142 | Female | 3.2 | 720 | 500 |
| 1020 | Female | 3.2 | 600 | 630 |
| 1201 | Female | 3.21 | 760 | 660 |
| 1089 | Female | 3.25 | 500 | 600 |
| 1133 | Female | 3.27 | 450 | 550 |
| 1144 | Female | 3.27 | 640 | 600 |
| 1163 | Female | 3.3 | 600 | 590 |
| 1038 | Female | 3.3 | 680 | 650 |
| 1069 | Female | 3.3 | 600 | 490 |
| 1033 | Female | 3.3 | 650 | 600 |
| 1115 | Female | 3.3 | 580 | 620 |
| 1057 | Female | 3.3 | 600 | 600 |
| 1037 | Female | 3.3 | 500 | 400 |
| 1109 | Female | 3.31 | 500 | 490 |
| 1215 | Female | 3.33 | 650 | 630 |
| 1078 | Female | 3.33 | 570 | 490 |
| 1060 | Female | 3.36 | 540 | 550 |
| 1030 | Female | 3.36 | 450 | 450 |
| 1129 | Female | 3.4 | 500 | 540 |
| 1100 | Female | 3.4 | 490 | 520 |
| 1196 | Female | 3.41 | 480 | 560 |
| 1046 | Female | 3.42 | 660 | 580 |
| 1165 | Female | 3.45 | 560 | 500 |
| 1148 | Female | 3.46 | 620 | 640 |
| 1082 | Female | 3.48 | 550 | 690 |
| 1096 | Female | 3.48 | 750 | 550 |
| 1168 | Female | 3.5 | 650 | 560 |
| 1091 | Female | 3.5 | 800 | 650 |
| 1018 | Female | 3.5 | 600 | 750 |
| 1181 | Female | 3.5 | 600 | 550 |
| 1123 | Female | 3.51 | 560 | 530 |
| 1177 | Female | 3.53 | 520 | 500 |
| 1063 | Female | 3.53 | 560 | 590 |
| 1015 | Female | 3.55 | 400 | 600 |
| 1058 | Female | 3.55 | 585 | 590 |
| 1160 | Female | 3.57 | 700 | 700 |
| 1159 | Female | 3.59 | 640 | 440 |
| 1202 | Female | 3.6 | 460 | 540 |
| 1004 | Female | 3.6 | 710 | 560 |
| 1086 | Female | 3.6 | 540 | 570 |
| 1035 | Female | 3.61 | 500 | 550 |
| 1187 | Female | 3.62 | 780 | 660 |
| 1149 | Female | 3.63 | 640 | 700 |
| 1110 | Female | 3.63 | 700 | 600 |
| 1011 | Female | 3.67 | 690 | 690 |
| 1044 | Female | 3.7 | 570 | 560 |
| 1145 | Female | 3.7 | 500 | 450 |
| 1207 | Female | 3.7 | 640 | 670 |
| 1052 | Female | 3.7 | 600 | 620 |
| 1218 | Female | 3.71 | 540 | 560 |
| 1174 | Female | 3.74 | 680 | 600 |
| 1118 | Female | 3.74 | 650 | 700 |
| 1031 | Female | 3.75 | 640 | 620 |
| 1073 | Female | 3.76 | 600 | 550 |
| 1005 | Female | 3.76 | 600 | 520 |
| 1055 | Female | 3.76 | 760 | 600 |
| 1204 | Female | 3.77 | 650 | 630 |
| 1199 | Female | 3.77 | 620 | 640 |
| 1134 | Female | 3.78 | 650 | 600 |
| 1208 | Female | 3.78 | 550 | 400 |
| 1010 | Female | 3.8 | 580 | 540 |
| 1197 | Female | 3.8 | 600 | 700 |
| 1205 | Female | 3.8 | 550 | 550 |
| 1077 | Female | 3.81 | 560 | 610 |
| 1105 | Female | 3.81 | 510 | 680 |
| 1179 | Female | 3.83 | 660 | 660 |
| 1023 | Female | 3.88 | 670 | 680 |
| 1094 | Female | 3.89 | 640 | 710 |
| 1067 | Female | 3.9 | 640 | 560 |
| 1065 | Female | 3.9 | 575 | 600 |
| 1081 | Female | 3.94 | 620 | 600 |
| 1014 | Female | 4 | 700 | 700 |
| 1075 | Female | 4 | 650 | 550 |
| 1214 | Female | 4 | 590 | 500 |
| 1157 | Male | 3.02 | 400 | 400 |
| 1047 | Male | 3.02 | 700 | 570 |
| 1084 | Male | 3.03 | 690 | 690 |
| 1140 | Male | 3.04 | 540 | 600 |
| 1152 | Male | 3.05 | 600 | 500 |
| 1130 | Male | 3.06 | 660 | 620 |
| 1151 | Male | 3.08 | 590 | 590 |
| 1053 | Male | 3.08 | 420 | 490 |
| 1097 | Male | 3.1 | 640 | 430 |
| 1137 | Male | 3.1 | 580 | 640 |
| 1090 | Male | 3.12 | 560 | 330 |
| 1185 | Male | 3.13 | 600 | 600 |
| 1101 | Male | 3.13 | 570 | 490 |
| 1209 | Male | 3.14 | 580 | 580 |
| 1041 | Male | 3.16 | 620 | 760 |
| 1220 | Male | 3.19 | 480 | 480 |
| 1153 | Male | 3.2 | 550 | 630 |
| 1071 | Male | 3.2 | 650 | 660 |
| 1119 | Male | 3.2 | 640 | 630 |
| 1045 | Male | 3.2 | 680 | 710 |
| 1217 | Male | 3.21 | 620 | 400 |
| 1200 | Male | 3.21 | 650 | 590 |
| 1222 | Male | 3.24 | 510 | 680 |
| 1175 | Male | 3.3 | 640 | 600 |
| 1170 | Male | 3.3 | 610 | 590 |
| 1016 | Male | 3.3 | 640 | 600 |
| 1095 | Male | 3.33 | 690 | 650 |
| 1212 | Male | 3.35 | 570 | 480 |
| 1098 | Male | 3.36 | 540 | 520 |
| 1027 | Male | 3.36 | 550 | 600 |
| 1017 | Male | 3.38 | 720 | 580 |
| 1087 | Male | 3.4 | 580 | 570 |
| 1161 | Male | 3.4 | 600 | 630 |
| 1036 | Male | 3.42 | 530 | 760 |
| 1221 | Male | 3.42 | 590 | 610 |
| 1194 | Male | 3.46 | 640 | 600 |
| 1009 | Male | 3.48 | 690 | 620 |
| 1029 | Male | 3.5 | 680 | 680 |
| 1048 | Male | 3.51 | 650 | 590 |
| 1024 | Male | 3.51 | 600 | 730 |
| 1034 | Male | 3.53 | 670 | 630 |
| 1206 | Male | 3.53 | 620 | 630 |
| 1111 | Male | 3.54 | 540 | 570 |
| 1150 | Male | 3.55 | 630 | 650 |
| 1106 | Male | 3.57 | 620 | 500 |
| 1042 | Male | 3.66 | 640 | 640 |
| 1169 | Male | 3.67 | 650 | 670 |
| 1019 | Male | 3.7 | 600 | 720 |
| 1080 | Male | 3.72 | 660 | 660 |
| 1079 | Male | 3.72 | 700 | 640 |
| 1059 | Male | 3.72 | 800 | 750 |
| 1173 | Male | 3.73 | 580 | 590 |
| 1166 | Male | 3.74 | 700 | 520 |
| 1070 | Male | 3.76 | 600 | 610 |
| 1062 | Male | 3.76 | 670 | 670 |
| 1226 | Male | 3.78 | 630 | 520 |
| 1162 | Male | 3.83 | 710 | 710 |
| 1006 | Male | 3.86 | 610 | 720 |
| 1180 | Male | 3.86 | 720 | 500 |
| 1172 | Male | 3.87 | 780 | 580 |
| 1122 | Male | 3.88 | 670 | 510 |
| 1131 | Male | 3.92 | 730 | 800 |
| 1007 | Male | 3.94 | 710 | 670 |
| 1028 | Male | 4 | 610 | 600 |
プログラムを開いて実行し、出力を確認して、クエリ結果がまず「gender」、次に「GPA」でソートされていることを確認してください。 上記のプログラムに関して、いくつか指摘しておくべき点があります：
ORDER BYには一つ以上の列を使用してデータをソートできます。複数の列名を区切るにはコンマを使用します。この例では、「gender」と「GPA」の2つの列が使用されています。したがって、データはまず「gender」別でソートされ、その後に「GPA」でソートされます。
デフォルトでは、列の値は昇順にソートされます。例えば、「gender」には女性と男性の2つの値があります。クエリの結果では、アルファベット順に”Female”のレコードが先に表示され、その後に”Male”のレコードが表示されます。GPAの順序については、数値であるため、各性別グループ内でGPAの数値でオブザベーションをソートします。
WHERE句は、SATの言語スコアが欠落しておらず、GPAが3より大きいオブザベーションを選択するために使用されます。「is not null」と「is not missing」は、欠落値が含まれていないことを示すために交換が可能です。
PROC SORTと同様に、デフォルトの昇順を降順に変更したい場合は、列名の後にDESCを指定するだけです。
例#
次のプログラムは、性別の値で降順に、GPAで昇順にデータsurvey.sas7bdaをソートします：
proc sql;
select ID, Gender, GPA, SATM, SATV
from phc6089.survey
where SATV is not null and GPA > 3
order by Gender desc, 3 ;
quit;
| id | Gender | GPA | SATM | SATV |
|---|---|---|---|---|
| 1047 | Male | 3.02 | 700 | 570 |
| 1157 | Male | 3.02 | 400 | 400 |
| 1084 | Male | 3.03 | 690 | 690 |
| 1140 | Male | 3.04 | 540 | 600 |
| 1152 | Male | 3.05 | 600 | 500 |
| 1130 | Male | 3.06 | 660 | 620 |
| 1053 | Male | 3.08 | 420 | 490 |
| 1151 | Male | 3.08 | 590 | 590 |
| 1137 | Male | 3.1 | 580 | 640 |
| 1097 | Male | 3.1 | 640 | 430 |
| 1090 | Male | 3.12 | 560 | 330 |
| 1185 | Male | 3.13 | 600 | 600 |
| 1101 | Male | 3.13 | 570 | 490 |
| 1209 | Male | 3.14 | 580 | 580 |
| 1041 | Male | 3.16 | 620 | 760 |
| 1220 | Male | 3.19 | 480 | 480 |
| 1045 | Male | 3.2 | 680 | 710 |
| 1071 | Male | 3.2 | 650 | 660 |
| 1119 | Male | 3.2 | 640 | 630 |
| 1153 | Male | 3.2 | 550 | 630 |
| 1200 | Male | 3.21 | 650 | 590 |
| 1217 | Male | 3.21 | 620 | 400 |
| 1222 | Male | 3.24 | 510 | 680 |
| 1175 | Male | 3.3 | 640 | 600 |
| 1016 | Male | 3.3 | 640 | 600 |
| 1170 | Male | 3.3 | 610 | 590 |
| 1095 | Male | 3.33 | 690 | 650 |
| 1212 | Male | 3.35 | 570 | 480 |
| 1098 | Male | 3.36 | 540 | 520 |
| 1027 | Male | 3.36 | 550 | 600 |
| 1017 | Male | 3.38 | 720 | 580 |
| 1161 | Male | 3.4 | 600 | 630 |
| 1087 | Male | 3.4 | 580 | 570 |
| 1221 | Male | 3.42 | 590 | 610 |
| 1036 | Male | 3.42 | 530 | 760 |
| 1194 | Male | 3.46 | 640 | 600 |
| 1009 | Male | 3.48 | 690 | 620 |
| 1029 | Male | 3.5 | 680 | 680 |
| 1048 | Male | 3.51 | 650 | 590 |
| 1024 | Male | 3.51 | 600 | 730 |
| 1034 | Male | 3.53 | 670 | 630 |
| 1206 | Male | 3.53 | 620 | 630 |
| 1111 | Male | 3.54 | 540 | 570 |
| 1150 | Male | 3.55 | 630 | 650 |
| 1106 | Male | 3.57 | 620 | 500 |
| 1042 | Male | 3.66 | 640 | 640 |
| 1169 | Male | 3.67 | 650 | 670 |
| 1019 | Male | 3.7 | 600 | 720 |
| 1080 | Male | 3.72 | 660 | 660 |
| 1079 | Male | 3.72 | 700 | 640 |
| 1059 | Male | 3.72 | 800 | 750 |
| 1173 | Male | 3.73 | 580 | 590 |
| 1166 | Male | 3.74 | 700 | 520 |
| 1070 | Male | 3.76 | 600 | 610 |
| 1062 | Male | 3.76 | 670 | 670 |
| 1226 | Male | 3.78 | 630 | 520 |
| 1162 | Male | 3.83 | 710 | 710 |
| 1180 | Male | 3.86 | 720 | 500 |
| 1006 | Male | 3.86 | 610 | 720 |
| 1172 | Male | 3.87 | 780 | 580 |
| 1122 | Male | 3.88 | 670 | 510 |
| 1131 | Male | 3.92 | 730 | 800 |
| 1007 | Male | 3.94 | 710 | 670 |
| 1028 | Male | 4 | 610 | 600 |
| 1219 | Female | 3.01 | 630 | 590 |
| 1039 | Female | 3.02 | 560 | 560 |
| 1125 | Female | 3.08 | 470 | 510 |
| 1072 | Female | 3.1 | 570 | 570 |
| 1139 | Female | 3.1 | 530 | 600 |
| 1068 | Female | 3.1 | 550 | 610 |
| 1116 | Female | 3.1 | 560 | 550 |
| 1203 | Female | 3.1 | 500 | 610 |
| 1138 | Female | 3.12 | 560 | 580 |
| 1120 | Female | 3.16 | 680 | 670 |
| 1020 | Female | 3.2 | 600 | 630 |
| 1142 | Female | 3.2 | 720 | 500 |
| 1102 | Female | 3.2 | 620 | 630 |
| 1201 | Female | 3.21 | 760 | 660 |
| 1089 | Female | 3.25 | 500 | 600 |
| 1144 | Female | 3.27 | 640 | 600 |
| 1133 | Female | 3.27 | 450 | 550 |
| 1163 | Female | 3.3 | 600 | 590 |
| 1038 | Female | 3.3 | 680 | 650 |
| 1037 | Female | 3.3 | 500 | 400 |
| 1069 | Female | 3.3 | 600 | 490 |
| 1033 | Female | 3.3 | 650 | 600 |
| 1057 | Female | 3.3 | 600 | 600 |
| 1115 | Female | 3.3 | 580 | 620 |
| 1109 | Female | 3.31 | 500 | 490 |
| 1078 | Female | 3.33 | 570 | 490 |
| 1215 | Female | 3.33 | 650 | 630 |
| 1060 | Female | 3.36 | 540 | 550 |
| 1030 | Female | 3.36 | 450 | 450 |
| 1129 | Female | 3.4 | 500 | 540 |
| 1100 | Female | 3.4 | 490 | 520 |
| 1196 | Female | 3.41 | 480 | 560 |
| 1046 | Female | 3.42 | 660 | 580 |
| 1165 | Female | 3.45 | 560 | 500 |
| 1148 | Female | 3.46 | 620 | 640 |
| 1082 | Female | 3.48 | 550 | 690 |
| 1096 | Female | 3.48 | 750 | 550 |
| 1181 | Female | 3.5 | 600 | 550 |
| 1168 | Female | 3.5 | 650 | 560 |
| 1018 | Female | 3.5 | 600 | 750 |
| 1091 | Female | 3.5 | 800 | 650 |
| 1123 | Female | 3.51 | 560 | 530 |
| 1177 | Female | 3.53 | 520 | 500 |
| 1063 | Female | 3.53 | 560 | 590 |
| 1058 | Female | 3.55 | 585 | 590 |
| 1015 | Female | 3.55 | 400 | 600 |
| 1160 | Female | 3.57 | 700 | 700 |
| 1159 | Female | 3.59 | 640 | 440 |
| 1086 | Female | 3.6 | 540 | 570 |
| 1004 | Female | 3.6 | 710 | 560 |
| 1202 | Female | 3.6 | 460 | 540 |
| 1035 | Female | 3.61 | 500 | 550 |
| 1187 | Female | 3.62 | 780 | 660 |
| 1149 | Female | 3.63 | 640 | 700 |
| 1110 | Female | 3.63 | 700 | 600 |
| 1011 | Female | 3.67 | 690 | 690 |
| 1044 | Female | 3.7 | 570 | 560 |
| 1145 | Female | 3.7 | 500 | 450 |
| 1052 | Female | 3.7 | 600 | 620 |
| 1207 | Female | 3.7 | 640 | 670 |
| 1218 | Female | 3.71 | 540 | 560 |
| 1174 | Female | 3.74 | 680 | 600 |
| 1118 | Female | 3.74 | 650 | 700 |
| 1031 | Female | 3.75 | 640 | 620 |
| 1073 | Female | 3.76 | 600 | 550 |
| 1055 | Female | 3.76 | 760 | 600 |
| 1005 | Female | 3.76 | 600 | 520 |
| 1204 | Female | 3.77 | 650 | 630 |
| 1199 | Female | 3.77 | 620 | 640 |
| 1134 | Female | 3.78 | 650 | 600 |
| 1208 | Female | 3.78 | 550 | 400 |
| 1010 | Female | 3.8 | 580 | 540 |
| 1205 | Female | 3.8 | 550 | 550 |
| 1197 | Female | 3.8 | 600 | 700 |
| 1077 | Female | 3.81 | 560 | 610 |
| 1105 | Female | 3.81 | 510 | 680 |
| 1179 | Female | 3.83 | 660 | 660 |
| 1023 | Female | 3.88 | 670 | 680 |
| 1094 | Female | 3.89 | 640 | 710 |
| 1067 | Female | 3.9 | 640 | 560 |
| 1065 | Female | 3.9 | 575 | 600 |
| 1081 | Female | 3.94 | 620 | 600 |
| 1075 | Female | 4 | 650 | 550 |
| 1014 | Female | 4 | 700 | 700 |
| 1214 | Female | 4 | 590 | 500 |
前の例のプログラムとは2つの点で異なります。DESCが「Gender」の後に追加され、データを降順にソートするように指定されています。列名の代わりにSELECT句での位置を参照するもう一つの方法があります。「GPA」は3番目にリストされているので、「GPA」を指定するために3を使用できます。
プログラムを開いて実行し、出力から「Gender」の降順および「GPA」の昇順でクエリの結果が並んでいることを確認してください。
ここまでで、ORDER BYがPROC SORTと同じ機能を持つと考えるかもしれません。実際にはそれ以上のことができます。次の例で確認してみましょう。
例#
次のプログラムは、前と同様に性別で降順にソートし、次にSATの数学と言語のスコアの平均値で昇順にソートします：
proc sql;
select *
from phc6089.survey
where SATV is not null and GPA>3
order by Gender desc, mean(SATM,SATV) ;
quit;
| id | Gender | GPA | SmokeCigarettes | SATM | SATV |
|---|---|---|---|---|---|
| 1157 | Male | 3.02 | No | 400 | 400 |
| 1090 | Male | 3.12 | No | 560 | 330 |
| 1053 | Male | 3.08 | No | 420 | 490 |
| 1220 | Male | 3.19 | No | 480 | 480 |
| 1217 | Male | 3.21 | No | 620 | 400 |
| 1212 | Male | 3.35 | No | 570 | 480 |
| 1101 | Male | 3.13 | No | 570 | 490 |
| 1098 | Male | 3.36 | No | 540 | 520 |
| 1097 | Male | 3.1 | No | 640 | 430 |
| 1152 | Male | 3.05 | No | 600 | 500 |
| 1111 | Male | 3.54 | No | 540 | 570 |
| 1106 | Male | 3.57 | No | 620 | 500 |
| 1140 | Male | 3.04 | Yes | 540 | 600 |
| 1226 | Male | 3.78 | No | 630 | 520 |
| 1087 | Male | 3.4 | No | 580 | 570 |
| 1027 | Male | 3.36 | No | 550 | 600 |
| 1209 | Male | 3.14 | No | 580 | 580 |
| 1173 | Male | 3.73 | No | 580 | 590 |
| 1122 | Male | 3.88 | No | 670 | 510 |
| 1153 | Male | 3.2 | No | 550 | 630 |
| 1151 | Male | 3.08 | No | 590 | 590 |
| 1222 | Male | 3.24 | No | 510 | 680 |
| 1185 | Male | 3.13 | No | 600 | 600 |
| 1221 | Male | 3.42 | Yes | 590 | 610 |
| 1170 | Male | 3.3 | No | 610 | 590 |
| 1070 | Male | 3.76 | No | 600 | 610 |
| 1028 | Male | 4 | No | 610 | 600 |
| 1137 | Male | 3.1 | No | 580 | 640 |
| 1166 | Male | 3.74 | No | 700 | 520 |
| 1180 | Male | 3.86 | No | 720 | 500 |
| 1161 | Male | 3.4 | No | 600 | 630 |
| 1175 | Male | 3.3 | Yes | 640 | 600 |
| 1016 | Male | 3.3 | No | 640 | 600 |
| 1194 | Male | 3.46 | No | 640 | 600 |
| 1048 | Male | 3.51 | No | 650 | 590 |
| 1200 | Male | 3.21 | No | 650 | 590 |
| 1206 | Male | 3.53 | No | 620 | 630 |
| 1119 | Male | 3.2 | No | 640 | 630 |
| 1047 | Male | 3.02 | No | 700 | 570 |
| 1150 | Male | 3.55 | No | 630 | 650 |
| 1042 | Male | 3.66 | No | 640 | 640 |
| 1130 | Male | 3.06 | No | 660 | 620 |
| 1036 | Male | 3.42 | No | 530 | 760 |
| 1034 | Male | 3.53 | No | 670 | 630 |
| 1017 | Male | 3.38 | No | 720 | 580 |
| 1071 | Male | 3.2 | No | 650 | 660 |
| 1009 | Male | 3.48 | No | 690 | 620 |
| 1080 | Male | 3.72 | No | 660 | 660 |
| 1169 | Male | 3.67 | No | 650 | 670 |
| 1019 | Male | 3.7 | No | 600 | 720 |
| 1006 | Male | 3.86 | No | 610 | 720 |
| 1024 | Male | 3.51 | No | 600 | 730 |
| 1079 | Male | 3.72 | No | 700 | 640 |
| 1062 | Male | 3.76 | No | 670 | 670 |
| 1095 | Male | 3.33 | No | 690 | 650 |
| 1172 | Male | 3.87 | Yes | 780 | 580 |
| 1029 | Male | 3.5 | No | 680 | 680 |
| 1041 | Male | 3.16 | No | 620 | 760 |
| 1007 | Male | 3.94 | No | 710 | 670 |
| 1084 | Male | 3.03 | No | 690 | 690 |
| 1045 | Male | 3.2 | No | 680 | 710 |
| 1162 | Male | 3.83 | No | 710 | 710 |
| 1131 | Male | 3.92 | No | 730 | 800 |
| 1059 | Male | 3.72 | No | 800 | 750 |
| 1037 | Female | 3.3 | No | 500 | 400 |
| 1030 | Female | 3.36 | No | 450 | 450 |
| 1145 | Female | 3.7 | No | 500 | 450 |
| 1208 | Female | 3.78 | No | 550 | 400 |
| 1125 | Female | 3.08 | No | 470 | 510 |
| 1109 | Female | 3.31 | No | 500 | 490 |
| 1133 | Female | 3.27 | No | 450 | 550 |
| 1015 | Female | 3.55 | No | 400 | 600 |
| 1202 | Female | 3.6 | No | 460 | 540 |
| 1100 | Female | 3.4 | No | 490 | 520 |
| 1177 | Female | 3.53 | No | 520 | 500 |
| 1196 | Female | 3.41 | No | 480 | 560 |
| 1129 | Female | 3.4 | No | 500 | 540 |
| 1035 | Female | 3.61 | No | 500 | 550 |
| 1078 | Female | 3.33 | No | 570 | 490 |
| 1165 | Female | 3.45 | No | 560 | 500 |
| 1159 | Female | 3.59 | No | 640 | 440 |
| 1069 | Female | 3.3 | No | 600 | 490 |
| 1060 | Female | 3.36 | No | 540 | 550 |
| 1123 | Female | 3.51 | No | 560 | 530 |
| 1214 | Female | 4 | No | 590 | 500 |
| 1089 | Female | 3.25 | Yes | 500 | 600 |
| 1218 | Female | 3.71 | No | 540 | 560 |
| 1205 | Female | 3.8 | No | 550 | 550 |
| 1086 | Female | 3.6 | No | 540 | 570 |
| 1116 | Female | 3.1 | No | 560 | 550 |
| 1203 | Female | 3.1 | No | 500 | 610 |
| 1039 | Female | 3.02 | No | 560 | 560 |
| 1010 | Female | 3.8 | No | 580 | 540 |
| 1005 | Female | 3.76 | No | 600 | 520 |
| 1044 | Female | 3.7 | No | 570 | 560 |
| 1139 | Female | 3.1 | No | 530 | 600 |
| 1072 | Female | 3.1 | No | 570 | 570 |
| 1138 | Female | 3.12 | No | 560 | 580 |
| 1181 | Female | 3.5 | No | 600 | 550 |
| 1073 | Female | 3.76 | No | 600 | 550 |
| 1063 | Female | 3.53 | No | 560 | 590 |
| 1068 | Female | 3.1 | No | 550 | 610 |
| 1077 | Female | 3.81 | No | 560 | 610 |
| 1065 | Female | 3.9 | No | 575 | 600 |
| 1058 | Female | 3.55 | No | 585 | 590 |
| 1163 | Female | 3.3 | No | 600 | 590 |
| 1105 | Female | 3.81 | No | 510 | 680 |
| 1075 | Female | 4 | No | 650 | 550 |
| 1067 | Female | 3.9 | No | 640 | 560 |
| 1057 | Female | 3.3 | No | 600 | 600 |
| 1115 | Female | 3.3 | No | 580 | 620 |
| 1168 | Female | 3.5 | No | 650 | 560 |
| 1081 | Female | 3.94 | No | 620 | 600 |
| 1142 | Female | 3.2 | No | 720 | 500 |
| 1052 | Female | 3.7 | No | 600 | 620 |
| 1219 | Female | 3.01 | No | 630 | 590 |
| 1020 | Female | 3.2 | No | 600 | 630 |
| 1082 | Female | 3.48 | No | 550 | 690 |
| 1144 | Female | 3.27 | No | 640 | 600 |
| 1046 | Female | 3.42 | No | 660 | 580 |
| 1033 | Female | 3.3 | No | 650 | 600 |
| 1134 | Female | 3.78 | No | 650 | 600 |
| 1102 | Female | 3.2 | No | 620 | 630 |
| 1148 | Female | 3.46 | No | 620 | 640 |
| 1031 | Female | 3.75 | No | 640 | 620 |
| 1199 | Female | 3.77 | No | 620 | 640 |
| 1004 | Female | 3.6 | No | 710 | 560 |
| 1174 | Female | 3.74 | No | 680 | 600 |
| 1215 | Female | 3.33 | No | 650 | 630 |
| 1204 | Female | 3.77 | No | 650 | 630 |
| 1110 | Female | 3.63 | No | 700 | 600 |
| 1096 | Female | 3.48 | No | 750 | 550 |
| 1197 | Female | 3.8 | No | 600 | 700 |
| 1207 | Female | 3.7 | No | 640 | 670 |
| 1179 | Female | 3.83 | No | 660 | 660 |
| 1038 | Female | 3.3 | No | 680 | 650 |
| 1149 | Female | 3.63 | No | 640 | 700 |
| 1023 | Female | 3.88 | No | 670 | 680 |
| 1018 | Female | 3.5 | No | 600 | 750 |
| 1120 | Female | 3.16 | No | 680 | 670 |
| 1118 | Female | 3.74 | No | 650 | 700 |
| 1094 | Female | 3.89 | No | 640 | 710 |
| 1055 | Female | 3.76 | No | 760 | 600 |
| 1011 | Female | 3.67 | Yes | 690 | 690 |
| 1160 | Female | 3.57 | No | 700 | 700 |
| 1014 | Female | 4 | No | 700 | 700 |
| 1201 | Female | 3.21 | Yes | 760 | 660 |
| 1187 | Female | 3.62 | No | 780 | 660 |
| 1091 | Female | 3.5 | No | 800 | 650 |
すべての列がクエリで使用されるため、SELECTの後に*を使用してすべての列を指定します。WHERE句は同じままです。ORDER BY句では、「Gender」のほかに、SATMとSATVの平均スコアを計算する関数が使用され、その計算結果を使用して各性別グループ内でデータをソートします。同じ結果を得るために、データステップではどれだけの手順が必要になるでしょうか。
プログラムを開いて実行し、出力からデータが期待される順序でソートされていることを確認してください。
もう一つ、プログラムを実行するときにログウィンドウにNoteが表示されるかもしれません。
NOTE: 指定したクエリにはSELECT句にない項目によるソートが含まれます。
これは、MEAN(SATM, SATV)がSELECT句にはリストされず、ORDER BY句のみに表示されるためです。
データの要約とグループ化#
前のセクションでは、SQLプロシージャを使用して詳細なレポートを生成しました。時には、データを探索するために要約レポートも必要です。そのためには、PROC SQLで要約関数やGROUP BY句が必要になります。
データステップで使用される多くの要約関数も、PROC SQLでうまく動作します。以下は、使用できる要約関数の一例です：
要約関数 |
説明 |
|---|---|
AVG, MEAN |
値の平均または平均値 |
COUNT, FREQ, N |
欠落していない値の数 |
CSS |
修正二乗和 |
CV |
変動係数（パーセント） |
MAX |
最大値 |
MIN |
最小値 |
NMISS |
欠落値の数 |
PRT |
学生のtの絶対値が大きい確率 |
RANGE |
値の範囲 |
STD |
標準偏差 |
STDERR |
平均の標準誤差 |
SUM |
値の合計 |
T |
仮説検定の学生のt値 |
USS |
非修正二乗和 |
VAR |
分散 |
注：一部の関数には複数の名前があります。最初にリストされているのがSQLの名前です。
次に、これらの関数がPROC SQLでどのように計算を行うかを例を通して見ていきます。その途中で、GROUP BY句を紹介し、関数と一緒に使用します。
例#
次のプログラムは、AVG()関数を使用して、SATの数学と言語テストの平均スコアを計算します：
proc sql;
select avg(SATM) as average_Math,
avg(SATV) as average_Verbal
from phc6089.survey;
quit
| average_Math | average_Verbal |
|---|---|
| 599.0046 | 580.3256 |
まずプログラムを開いて実行してください。出力を確認すると、SATMとSATVの2つの全体の平均スコアが別々に計算されているのがわかります。出力ウィンドウには1つのオブザベーションしかありません。 コード内の関数を確認しましょう。平均を計算するために、この場合、MEAN()またはAVG()のどちらかを使用できます。関数AVG()の中には引数（列）が1つだけあります。したがって、統計は1つの列のすべての行に対して計算されます。
AVG(STAM)—SATMの全体平均スコア
AVG(STAV)—SATVの全体平均スコア
非常に簡単ですね。関数に引数をもう1つ追加してみましょう。出力には何件のオブザベーションが表示されるでしょうか？
例#
次のプログラムでは、関数MEAN()の引数として2つの列を使用します：
proc sql;
select mean(SATM, SATV) as average
from phc6089.survey;
quit;
| average |
|---|
| 700 |
| 600 |
| 470 |
| 635 |
| 560 |
| 665 |
| 690 |
| 595 |
| 655 |
| 560 |
| 690 |
| . |
| 515 |
| 700 |
| 500 |
| 620 |
| 650 |
| 675 |
| 660 |
| 615 |
| 450 |
| 650 |
| 675 |
| 665 |
| 620 |
| 560 |
| 575 |
| 605 |
| 680 |
| 450 |
| 630 |
| 350 |
| 625 |
| 650 |
| 525 |
| 645 |
| 450 |
| 665 |
| 560 |
| . |
| 690 |
| 640 |
| 615 |
| 565 |
| 695 |
| 620 |
| 635 |
| 620 |
| 630 |
| 450 |
| 600 |
| 610 |
| 455 |
| 605 |
| 680 |
| 550 |
| 600 |
| 587.5 |
| 775 |
| 545 |
| 615 |
| 670 |
| 575 |
| 625 |
| 587.5 |
| 425 |
| 600 |
| 580 |
| 545 |
| 605 |
| 655 |
| 570 |
| 575 |
| 475 |
| 600 |
| 580 |
| 585 |
| 530 |
| 670 |
| 660 |
| 610 |
| 620 |
| 570 |
| 690 |
| 710 |
| 555 |
| 575 |
| . |
| 550 |
| 445 |
| 725 |
| 575 |
| 560 |
| 675 |
| 670 |
| 650 |
| 535 |
| 530 |
| 650 |
| 505 |
| 530 |
| 625 |
| 475 |
| . |
| 595 |
| 560 |
| 580 |
| 525 |
| 495 |
| 650 |
| 555 |
| . |
| 600 |
| 530 |
| 600 |
| 555 |
| 525 |
| 675 |
| 635 |
| 675 |
| 475 |
| 590 |
| 545 |
| 435 |
| 490 |
| 645 |
| 625 |
| 610 |
| 520 |
| 640 |
| 765 |
| 420 |
| 500 |
| 625 |
| 585 |
| 625 |
| 610 |
| 570 |
| 565 |
| 570 |
| . |
| 610 |
| 525 |
| 620 |
| 475 |
| 590 |
| 600 |
| 630 |
| 670 |
| 640 |
| 590 |
| 550 |
| 590 |
| 485 |
| . |
| 660 |
| 400 |
| 540 |
| 540 |
| 700 |
| 615 |
| 710 |
| 595 |
| 625 |
| 530 |
| 610 |
| 535 |
| 605 |
| 660 |
| 600 |
| 500 |
| 680 |
| 585 |
| 640 |
| 620 |
| 595 |
| 510 |
| 650 |
| 660 |
| 610 |
| 575 |
| 550 |
| 505 |
| . |
| 600 |
| 530 |
| 720 |
| 610 |
| 590 |
| 540 |
| 530 |
| . |
| 500 |
| 620 |
| 555 |
| 520 |
| 650 |
| 525 |
| 630 |
| 620 |
| 710 |
| 500 |
| 555 |
| 640 |
| 550 |
| 625 |
| 655 |
| 475 |
| 580 |
| 625 |
| 610 |
| 525 |
| 425 |
| 545 |
| 640 |
| 640 |
| 510 |
| 550 |
| 610 |
| 480 |
| 600 |
| 595 |
| . |
| 640 |
| 635 |
| 575 |
プログラムを少し変更しました。「SATM」と「SATV」の両方が引数として関数内で指定されています。プログラムを開いて実行してください。出力には226オブザベーションが表示され、これは元の調査データと同じです。
要約関数の引数として複数の列を追加すると、SASは各行の列全体にわたって計算を行い、上記の出力を生成します。
この場合、要約関数はもはや集計を行っていません。SASは次にBASE SAS内の同名関数を探します。あれば、各行に対して計算が行われ、なければログウィンドウにエラーメッセージが表示されます。MEAN()をAVG()に変更してみて、何が起こるか確認してください。
ERROR: 関数AVGが見つかりません。
例#
次のプログラムでは、1つの引数だけをMEAN()に使用しますが、SELECT句にもう1つ列を追加します：
proc sql;
select Gender,
mean(SATM) as average_Math
from phc6089.survey;
quit;
| Gender | average_Math |
|---|---|
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Male | 599.0046 |
| Female | 599.0046 |
| Male | 599.0046 |
上記のプログラムでは、SELECT文が再び変更されました。今回は、MEAN()関数の引数は1つのみで、「SATM」の全体平均を計算します。関数の外側には、別の列も選択されています。このプログラムはどのような出力結果を生み出すでしょうか？
プログラムを開いて実行してみてください。出力結果が226行あることに驚くかもしれません。出力結果をよく見ると、上記のコードによって2つのことが行われていることがわかります。
全生徒の数学成績の全体平均が計算されました。
「Gender」はMEAN()関数の引数ではないため、出力にはすべての行が表示されています。
全体平均の数学成績は、各行に繰り返し表示されます。ログウィンドウには、以下のようなメッセージが表示されることがあります。このようなプログラムを実行すると、SASは最初に統計量を計算し、それを他の列とマージします。これが「再マージ」が発生する仕組みです。
NOTE: クエリは元のデータに要約統計量の結果を再マージします。
上記の出力結果は、私たちが望んでいたものではありません。では、GROUP BY句を使用して、より適切な結果を得る方法を見てみましょう。
例#
次の例では、各性別グループの平均SAT数学成績を計算します。
proc sql;
select Gender,
mean(SATM) as average_Math
from phc6089.survey
group by Gender;
quit;
| Gender | average_Math |
|---|---|
| Female | 589.5082 |
| Male | 611.3298 |
上記のプログラムは、前の例にあるプログラムとほぼ同じですが、GROUP BY句が追加されています。これで、目的の結果、つまり女性と男性の生徒の平均「SATM」が得られました。もちろん、GROUP BY句には複数の列を追加して、さらに活用することができます。次の例で確認してみましょう。
例#
次のプログラムでは、GROUP BY句に「Gender」と「SmokeCigarettes」の両方を用いて、SAT数学スコアの平均を計算します。
proc sql;
select Gender, SmokeCigarettes,
mean(SATM) as average_Math
from phc6089.survey
group by 1, 2;
quit;
| Gender | SmokeCigarettes | average_Math |
|---|---|---|
| Female | No | 589.6552 |
| Female | Yes | 586.6667 |
| Male | No | 613.2353 |
| Male | Yes | 593.3333 |
プログラムを開いて実行し、出力結果を確認してください。ご覧のとおり、「SATM」の平均は、「gender」の各グループ（女性または男性）内の各喫煙グループ（はいまたはいいえ）に対して計算されています。
このプログラムについて、もう1点重要なことがあります。WHERE句と同様に、列はSELECT句における位置で参照することもできます。ここでは、1と2はそれぞれ「Gender」と「SmokeCigarettes」を表しています。
次に、SQLの特殊な要約関数であるCOUNT()について説明します。COUNT()関数は、欠損値ではない値をカウントするために使用できます。
例#
次の例では、調査データの行数、数学と語彙テストのスコアにおける欠損値ではないレコードの数、性別のユニークな値をカウントします。
proc sql;
select count(*) as No_obs,
count(SATM) as No_Math_records,
count(SATV) as No_Verbal_records,
count(distinct Gender) as Gender_group
from phc6089.survey;
quit;
| No_obs | No_Math_records | No_Verbal_records | Gender_group |
|---|---|---|---|
| 226 | 216 | 215 | 2 |
上記のコードは、COUNT()関数の3つの一般的な使用方法を示しています。
Count(*)は、テーブル内の行の総数をカウントします。COUNT()は、*を引数として使用できる唯一の関数です。
Count(column)は、列内の欠損値ではない値の数をカウントします。プログラムでは、数学と語彙のスコアの欠損値ではない値の数をカウントしています。
Count(distinct column)は、列内のユニークな値の総数をカウントします。上記の例では、性別のカテゴリ数をカウントしています。
プログラムを開いて実行し、出力結果を確認してください。テーブル内の欠損値をある程度把握していれば、最初の3つの数値が一致しないことに驚くことはないでしょう。調査データの行数は226です。「SATM」と「SATV」の欠損値ではない値の総数は、それぞれ216と215です。両方の数は226より小さいので、各列で欠損値があり、「SATV」には1つ多い欠損値があります。「Gender」には、”male”と”female”の2つのカテゴリのみがあります。そのため、最後のカウントは2です。
HAVING句の使用#
これまで、GROUP BY句を使用してデータをグループ化して要約する方法を学びました。場合によっては、結果から特定のグループを選択したいことがあります。これが、HAVING句が役立つ場面です。
例#
次のプログラムでは、各部署の平均給与を計算し、クエリの出力で必要な3つの部署を選択します。
proc sql;
select Department,
avg(Employee_annual_salary) as Avg_salary format=dollar12.2
from phc6089.salary
group by Department
having Department in ('LAW','FINANCE','FIRE')
order by Avg_salary;
quit;
| Department | Avg_salary |
|---|---|
| LAW | $71,082.20 |
| FINANCE | $82,184.00 |
| FIRE | $90,742.33 |
まず、プログラムを確認しましょう。コードは列「Department」を選択し、要約関数AVG()を使用して平均給与を計算します。SELECT文にもGROUP BY句が含まれているため、平均は各部署に対するものです。ユーザーは、’LAW’,’FINANCE’,’FIRE’の3つの部署のみに関心があります。そのため、HAVING句を使用して、この3つの部署のみを出力するように選択します。最後に、平均給与でデータをソートするように指示します。このプログラムには、これまで学んだすべての句が含まれていますが、WHERE句は除きます。WHERE句については、後で説明します。
プログラムを開いて実行し、出力結果を理解していることを確認してください。
上記のプログラムでHAVING句と同じことをWHERE句で行うことができるかどうか疑問に思うかもしれません。次の例のように、HAVING句をWHERE句に置き換えてみてください。同じ出力結果が得られます。
proc sql;
select Department,
avg(Employee_annual_salary) as Avg_salary format=dollar12.2
from phc6089.salary
where Department in ('LAW','FINANCE','FIRE')
group by Department
order by Avg_salary;
quit;
| Department | Avg_salary |
|---|---|
| LAW | $71,082.20 |
| FINANCE | $82,184.00 |
| FIRE | $90,742.33 |
ただし、この例だけでWHERE句とHAVING句が同じであると結論付けるのは早計です。両者には大きな違いがあります。一般的に、HAVING句は出力時にグループ化されたデータを制御し、WHERE句は行単位で入力データを制御します。これらの2つのコマンドについて、さらに例を見てみましょう。
例#
次のプログラムでは、各部署の平均給与を計算し、70,000ドルを超える平均給与を持つ部署を選択します。
proc sql;
select Department,
avg(Employee_annual_salary) as Avg_salary format=dollar12.2
from phc6089.salary
group by Department
having Avg_salary > 70000
order by Avg_salary;
quit;
| Department | Avg_salary |
|---|---|
| LAW | $71,082.20 |
| BOARD OF ELECTION | $74,988.00 |
| HEALTH | $75,066.86 |
| TRANSPORTN | $79,438.18 |
| POLICE | $81,850.26 |
| FINANCE | $82,184.00 |
| WATER MGMNT | $84,780.42 |
| PROCUREMENT | $89,236.00 |
| COMMUNITY DEVELOPMENT | $90,096.00 |
| DoIT | $90,252.00 |
| FIRE | $90,742.33 |
| ADMIN HEARNG | $91,980.00 |
| BUILDINGS | $94,793.01 |
このプログラムには、小さな変更が加えられています。HAVING句の条件を部署の平均給与が70,000ドル以上に変更しました。そのため、HAVING句で使用される式は要約関数です。また、データは平均値でソートされています。
プログラムを開いて実行し、出力結果を確認してください。期待される通り、平均給与が70,000ドルを超えるすべての部署がクエリの結果としてリストされています。
次に、WHERE句を使用して同じタスクを実行してみましょう。
proc sql;
select Department,
avg(Employee_annual_salary) as Avg_salary format=dollar12.2
from phc6089.salary
where calculated Avg_salary > 70000
group by Department
order by Avg_salary;
quit;
45 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
342 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
342 ! ods graphics on / outputfmt=png;
NOTE: HTML5(SASPY_INTERNAL) Bodyファイルの書き込み先: _TOMODS1
343
344 proc sql;
345 select Department,
346 avg(Employee_annual_salary) as Avg_salary format=dollar12.2
347 from phc6089.salary
348 where calculated Avg_salary > 70000
349 group by Department
350 order by Avg_salary;
NOTE: データファイルPHC6089.SALARY.DATAは別なホストにネイティブな形式が使用されているか、または
エンコーディングがセッションエンコーディングと一致していません。クロス環境データアク
セスが使用されるため、追加のCPUリソースが必要となり、パフォーマンスが低下します。
ERROR: 要約関数はSELECT句とHAVING句に限定されています。
NOTE: PROC SQLはNOEXECオプションを設定し、ステートメントの構文をチェックします。
351 quit;
NOTE: エラーが発生したため、このステップの処理を中止しました。
NOTE: PROCEDURE SQL処理(合計処理時間):
処理時間 0.00 秒
ユーザーCPU時間 0.00 秒
システムCPU時間 0.00 秒
メモリ 5600.12k
OSメモリ 25504.00k
タイムスタンプ 2024/06/06 午前08:51:36
ステップ数 22 スイッチ数 0
ページフォルト回数 0
ページリクレーム回数 80
ページスワップ回数 0
自発的コンテキストスイッチ回数 8
非自発的コンテキストスイッチ回数 0
ブロック入力操作回数 0
ブロック出力操作回数 8
352
353
354 ods html5 (id=saspy_internal) close;ods listing;
355
46 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
356
ERROR: 要約関数はSELECT句とHAVING句に限定されています。 None
計算された結果をWHERE句で使用するには、「CALCULATED」というキーワードを挿入する必要があることを覚えておいてください。SASからは次のようなエラーメッセージが表示されてしまいました。
ERROR: 要約関数はSELECT句とHAVING句に限定されています。
この例は、HAVING句とWHERE句の大きな違いを示しています。要約関数はHAVING句で使用できますが、WHERE句では使用できません。これは、HAVING句がグループ化されたデータで動作するのに対し、WHERE句は既存のデータまたは計算されたデータを1行ずつ評価するためです。
これらの2つの句に関するこれまでの経験から、HAVING句の方が使いやすく、どちらの状況にも使用できるため、HAVING句を使用したいと思うかもしれません。しかし、すぐに結論を出すのは早計です。次の例でさらに詳しく見ていきましょう。
例#
次の2つのプログラムは似ています。唯一の違いは、最初のプログラムがWHERE句を使用し、2番目のプログラムがHAVING句を使用していることです。両方のプログラムは、警察署の各職位にいる従業員の数をカウントするという同じタスクを実行しようとします。
proc sql;
select Position_Title,
count(*) as Employees
from phc6089.salary
where Department='POLICE'
group by Position_Title;
quit;
proc sql;
select Position_Title,
count(*) as Employees
from phc6089.salary
group by Position_Title
having Department='POLICE';
quit;
| Position Title | Employees |
|---|---|
| ACCOUNTANT II | 1 |
| CLINICAL THERAPIST III | 1 |
| CROSSING GUARD | 5 |
| CROSSING GUARD - PER AGREEMENT | 3 |
| DIR OF POLICE RECORDS | 1 |
| FISCAL ADMINISTRATOR | 1 |
| POLICE OFFICER | 85 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER / FLD TRNG OFFICER | 1 |
| SENIOR DATA ENTRY OPERATOR | 2 |
| SERGEANT | 11 |
| Position Title | Employees |
|---|---|
| ACCOUNTANT II | 1 |
| CLINICAL THERAPIST III | 2 |
| CROSSING GUARD | 5 |
| CROSSING GUARD | 5 |
| CROSSING GUARD | 5 |
| CROSSING GUARD | 5 |
| CROSSING GUARD | 5 |
| CROSSING GUARD - PER AGREEMENT | 3 |
| CROSSING GUARD - PER AGREEMENT | 3 |
| CROSSING GUARD - PER AGREEMENT | 3 |
| DIR OF POLICE RECORDS | 1 |
| FISCAL ADMINISTRATOR | 1 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER | 85 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | 13 |
| POLICE OFFICER / FLD TRNG OFFICER | 1 |
| SENIOR DATA ENTRY OPERATOR | 4 |
| SENIOR DATA ENTRY OPERATOR | 4 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
| SERGEANT | 11 |
両方のプログラムを実行してください。上の出力はWHERE句を使用したプログラムからの出力で、下の出力はHAVING句を使用したプログラムからの部分的な出力です。
2つの結果が大きく異なることに驚くかもしれません。上の出力のような結果を期待するでしょう。しかし、下の出力にははるかに多くの行があり、一部の数値は一致していません！コードを確認して、何が起こったのか理解しましょう。SELECT句には2つの列、「Position_Title」と要約関数count(*)があります。count(*)は、GROUP BY句で「Position_Title」を指定しているため、各職位グループの行の総数をカウントします。前の例にあるプログラムとは異なり、WHERE句とHAVING句で使用される式は、SELECT句に含まれていない別の列である「Department」を参照しています。そのため、2つのプログラムで異なる方法で処理がされます。
最初のプログラムはWHERE句を使用しています。WHERE句はSELECT句よりも前に行単位で処理されるため、まずデータから警察のレコードが選択されます。次に、部署内の各「Position_Title」の下にいる従業員の数をカウントします。たとえば、警察には「CLINICAL THERAPIST III」という職位の人は1人しかいません。そのため、カウントは1です。目的の出力結果が得られました。
一方、2番目のプログラムはHAVING句を使用しています。これは、次のプログラムと同等ですが、出力にはDepartment列が含まれていません。
proc sql;
select Position_Title,
Department,
count(*) as Employees
from phc6089.salary
group by Position_Title
having Department='POLICE';
quit;
| Position Title | Department | Employees |
|---|---|---|
| ACCOUNTANT II | POLICE | 1 |
| CLINICAL THERAPIST III | POLICE | 2 |
| CROSSING GUARD | POLICE | 5 |
| CROSSING GUARD | POLICE | 5 |
| CROSSING GUARD | POLICE | 5 |
| CROSSING GUARD | POLICE | 5 |
| CROSSING GUARD | POLICE | 5 |
| CROSSING GUARD - PER AGREEMENT | POLICE | 3 |
| CROSSING GUARD - PER AGREEMENT | POLICE | 3 |
| CROSSING GUARD - PER AGREEMENT | POLICE | 3 |
| DIR OF POLICE RECORDS | POLICE | 1 |
| FISCAL ADMINISTRATOR | POLICE | 1 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER | POLICE | 85 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER (ASSIGNED AS DETECTIVE) | POLICE | 13 |
| POLICE OFFICER / FLD TRNG OFFICER | POLICE | 1 |
| SENIOR DATA ENTRY OPERATOR | POLICE | 4 |
| SENIOR DATA ENTRY OPERATOR | POLICE | 4 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
| SERGEANT | POLICE | 11 |
このプログラムでは、GROUP BY句のために、すべての部署の各職位における従業員数をカウントします。たとえば、「CLINICAL THERAPIST III」という職位の人は、警察と保健の部署にそれぞれ1人ずついます。そのため、この職位の合計カウントは2です。要約関数とGROUP BY列の他にSELECT句に列が追加されているため、すべての行が出力に含まれ、各職位の数が示されます。たとえば、「CLINICAL THERAPIST III」という職位の両方のレコードの「Employees」の値は2です。最後にHAVING句の条件（Department=POLICE）を評価して、出力する行を選択します。そのため、2番目のクエリからの出力には、「CLINICAL THERAPIST III」という職位タイトルの下にEmployees=2が表示されます。
これまで、HAVING句とWHERE句の違いを示す2つの例を見てきました。両者を異なる方法で処理されるため、WHERE句とHAVING句を使用する場合は、自分のニーズに最適な方を選択してください。
最後に、HAVING句のもう1つの便利な機能を確認しましょう。
例#
次のプログラムでは、平均給与が全体な給与水準よりも低い部署を選択します。
proc sql;
select Department,
avg(Employee_annual_salary) as Avg_salary format=dollar12.2
from phc6089.salary
group by Department
having Avg_salary < (select avg(Employee_annual_salary) from phc6089.salary)
order by Avg_salary;
quit;
| Department | Avg_salary |
|---|---|
| DISABILITIES | $36,264.00 |
| OEMC | $49,116.80 |
| FAMILY & SUPPORT | $53,642.00 |
| PUBLIC LIBRARY | $59,030.00 |
| GENERAL SERVICES | $65,600.80 |
| BUSINESS AFFAIRS | $65,652.00 |
| CITY COUNCIL | $66,983.00 |
| AVIATION | $67,704.48 |
| STREETS & SAN | $68,625.08 |
| LAW | $71,082.20 |
| BOARD OF ELECTION | $74,988.00 |
| HEALTH | $75,066.86 |
このプログラムを見ても、HAVING句までは普通でないものは何も見つからないかもしれません。HAVING句の中では、以前のように標準的な式ではなく、クエリが使用されています。
(select avg(Employee_annual_salary) from stat482.salary)
このようなクエリは、サブクエリと呼ばれます。このクエリ式は、HAVING句またはWHERE句で使用できます。この例で使用されているサブクエリは、全体の平均給与を計算するためのもので、その結果は各部門の平均給与と比較されます。その後、HAVING句の「Less than」という条件を評価し、平均給与が低い部門を選択して出力します。
プログラムを開いて実行し、クエリの結果から説明の通りに部門の情報が選択されていることを確認してください。
複数のテーブルへのクエリ#
これまでのすべての例では、単一のテーブルに問い合わせをしていました。しかし実際には、FROM句に複数のテーブルを指定することができます。一度に複数のテーブルをクエリすることで、PROC SQLはデータ操作においてさらに強力になります。
次の例では、2つのテーブルを使用します。
Survey Data (survey.sas7bdat)には以下が含まれています。
id |
Gender |
GPA |
SmokeCig |
SATM |
SATV |
|---|---|---|---|---|---|
1001 |
Male |
2.67 |
No |
700 |
700 |
1002 |
Female |
2.98 |
No |
700 |
500 |
1003 |
Female |
2.67 |
No |
470 |
470 |
1004 |
Female |
3.6 |
No |
710 |
560 |
1005 |
Female |
3.76 |
No |
600 |
520 |
1006 |
Male |
3.86 |
No |
610 |
720 |
1007 |
Male |
3.94 |
No |
710 |
670 |
1008 |
Male |
2.8 |
Yes |
610 |
580 |
1009 |
Male |
3.48 |
No |
690 |
620 |
Survey2 Data (survey2.sas7bdat)には以下が含まれています。
id |
Seating |
DriverInfluen |
Height |
Weight |
|---|---|---|---|---|
1001 |
Middle |
No |
68 |
190 |
1002 |
Middle |
No |
54 |
110 |
1003 |
Middle |
No |
65 |
225 |
1004 |
Middle |
No |
52 |
135 |
1005 |
Back |
No |
72 |
128 |
1006 |
Middle |
No |
70 |
188 |
1007 |
Back |
No |
70 |
155 |
1008 |
Middle |
Yes |
68 |
160 |
1009 |
Front |
No |
72 |
160 |
これらの2つのテーブルをまだダウンロードしていない場合は、ダウンロードしてください。libnameをファイルの保存先のディレクトリに合わせて修正してください。
例#
次のプログラムは、2つの別々のテーブルsurveyとsurvey2から、生徒の属性情報を取得しようとしています。
proc sql;
create table demo_info as
select ID, Gender, Height, Weight
from phc6089.survey, phc6089.survey2;
quit;
53 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
417 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
417 ! ods graphics on / outputfmt=png;
NOTE: HTML5(SASPY_INTERNAL) Bodyファイルの書き込み先: _TOMODS1
418
419 proc sql;
420 create table demo_info as
421 select ID, Gender, Height, Weight
422 from phc6089.survey, phc6089.survey2;
NOTE: データファイルPHC6089.SURVEY.DATAは別なホストにネイティブな形式が使用されているか、または
エンコーディングがセッションエンコーディングと一致していません。クロス環境データアク
セスが使用されるため、追加のCPUリソースが必要となり、パフォーマンスが低下します。
NOTE: データファイルPHC6089.SURVEY2.DATAは別なホストにネイティブな形式が使用されているか、または
エンコーディングがセッションエンコーディングと一致していません。クロス環境データアク
セスが使用されるため、追加のCPUリソースが必要となり、パフォーマンスが低下します。
ERROR: 不確定な参照です。列IDは複数のテーブルに存在します。
NOTE: PROC SQLはNOEXECオプションを設定し、ステートメントの構文をチェックします。
423 quit;
NOTE: エラーが発生したため、このステップの処理を中止しました。
NOTE: PROCEDURE SQL処理(合計処理時間):
処理時間 0.00 秒
ユーザーCPU時間 0.00 秒
システムCPU時間 0.00 秒
メモリ 5935.56k
OSメモリ 25760.00k
タイムスタンプ 2024/06/06 午前08:52:12
ステップ数 27 スイッチ数 0
ページフォルト回数 0
ページリクレーム回数 156
ページスワップ回数 0
自発的コンテキストスイッチ回数 17
非自発的コンテキストスイッチ回数 0
ブロック入力操作回数 0
ブロック出力操作回数 0
424
425
426 ods html5 (id=saspy_internal) close;ods listing;
427
54 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
428
ERROR: 不確定な参照です。列IDは複数のテーブルに存在します。 None
コードを確認してみましょう。このSQLプロシージャでは、CREATE TABLE句を使用して、新しいテーブルをdemo_infoという名前で保存しています。その後のSELECT句は、2つのテーブルからID、gender、height、weightの各列を選択します。FROM句には、2つのテーブルの名前がリストされています。
プログラムを開いて実行します。CREATE TABLE句は出力を抑制するため、出力ウィンドウには何も表示されないはずです。一方、ログウィンドウを確認すると、”不確定な参照です。列IDは複数のテーブルに存在します” というエラーメッセージが表示されます。
ご覧のように、2つのテーブルにIDが存在し、同じ情報が含まれています。SELECT文の列が複数のテーブルに存在する場合、その列が選択されるテーブルを、下のように列名の前にテーブル名を追加して指定する必要があります。
Table.Column
そのため、前のプログラムを少し修正します。IDをsurvey.IDに変更します。これは、データsurveyの「ID」を使用することを意味します。もう1つの変更点は、テーブルの名前です。テーブルには、キーワードASを付けても付けなくても、エイリアスを付けることができます。次のプログラムでは、データsurveyにはS1、データsurvey2にはS2というエイリアスを使用しています。ご覧のとおり、永久ファイルに対しても、1レベルのエイリアスを使用することができます。これにより、指定が簡単になります！このように、IDはS1.IDとして指定することができます。
proc sql;
create table demo_info as
select s1.ID, Gender, Height, Weight
from phc6089.survey as s1, phc6089.survey2 as s2;
quit;
55 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
431 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
431 ! ods graphics on / outputfmt=png;
NOTE: HTML5(SASPY_INTERNAL) Bodyファイルの書き込み先: _TOMODS1
432
433 proc sql;
434 create table demo_info as
435 select s1.ID, Gender, Height, Weight
436 from phc6089.survey as s1, phc6089.survey2 as s2;
NOTE: データファイルPHC6089.SURVEY.DATAは別なホストにネイティブな形式が使用されているか、または
エンコーディングがセッションエンコーディングと一致していません。クロス環境データアク
セスが使用されるため、追加のCPUリソースが必要となり、パフォーマンスが低下します。
NOTE: データファイルPHC6089.SURVEY2.DATAは別なホストにネイティブな形式が使用されているか、または
エンコーディングがセッションエンコーディングと一致していません。クロス環境データアク
セスが使用されるため、追加のCPUリソースが必要となり、パフォーマンスが低下します。
NOTE: このクエリの実行には最適化できないデカルト積の結合が含まれます。
NOTE: テーブルWORK.DEMO_INFO(行数51076、列数4)が作成されました。
437 quit;
NOTE: PROCEDURE SQL処理(合計処理時間):
処理時間 0.01 秒
ユーザーCPU時間 0.01 秒
システムCPU時間 0.00 秒
メモリ 5932.12k
OSメモリ 25760.00k
タイムスタンプ 2024/06/06 午前08:52:25
ステップ数 28 スイッチ数 2
ページフォルト回数 0
ページリクレーム回数 590
ページスワップ回数 0
自発的コンテキストスイッチ回数 19
非自発的コンテキストスイッチ回数 0
ブロック入力操作回数 0
ブロック出力操作回数 3336
438
439
440 ods html5 (id=saspy_internal) close;ods listing;
441
56 SAS システム 2024年 6月 6日 木曜日 08時47分00秒
442
すべてうまくいっているようです。それではプログラムを開いて実行します。前述のように、CREATE TABLE文のため、出力はありません。ログウィンドウを確認すると、注意が必要な2つのNoteが表示されます（上記の最後の2つのNoteを参照）。
最初のNoteは、「このクエリの実行には最適化できないデカルト積の結合が含まれます。」です。デカルト積とは何でしょうか？直積とは、最初のテーブルの各行を、2番目のテーブルのすべての行と組み合わせたクエリの結果のことです。FROM句に複数のテーブルを指定しても、必要な行を選択するWHERE句を使用しない場合、デカルト積が生成されます。たとえば、次のプログラムを実行した場合：
PROC SQL;
Select *
from table1, table2;
Table1には3行、Table2にも3行あります。これらの直積は、(3*3)9行になります。
Table1
name |
value1 |
|---|---|
x |
1 |
y |
2 |
z |
3 |
(*)
Table2
name |
value2 |
|---|---|
A |
4 |
B |
5 |
C |
6 |
(=)
結果:
name |
value1 |
name |
value2 |
|---|---|---|---|
x |
1 |
A |
4 |
x |
1 |
B |
5 |
x |
1 |
C |
6 |
y |
2 |
A |
4 |
y |
2 |
B |
5 |
y |
2 |
C |
6 |
z |
3 |
A |
4 |
z |
3 |
B |
5 |
z |
3 |
C |
6 |
この例のプログラムには、WHERE句がありません。そのため直積を生成し、Noteを表示しました。SurveyとSurvey2の両方とも、テーブルに226行あります。クエリの結果は、(226*226) = 51076行になるはずです。そのため、もう1つのメモ、「テーブルWORK.DEMO_INFO(行数51076、列数4)が作成されました。」が表示されました。明らかに、これは正しいとは言えません。どのようにして目的の結果を得るのでしょうか？最後の仕上げをしましょう。
例#
次のプログラムは、2つのテーブルsurveyとsurvey2から、生徒の属性情報（ID、gender、height、weight）を選択します。
proc sql;
create table demo_info as
select s1.ID, Gender, Height, Weight
from phc6089.survey as s1, phc6089.survey2 as s2
where s1.ID = s2.ID;
select *
from demo_info
where ID < 1010;
quit;
| id | Gender | Height | Weight |
|---|---|---|---|
| 1001 | Male | 67 | 190 |
| 1002 | Female | 54 | 110 |
| 1003 | Female | 65 | 225 |
| 1004 | Female | 52 | 135 |
| 1005 | Female | 72 | 128 |
| 1006 | Male | 70 | 188 |
| 1007 | Male | 70 | 155 |
| 1008 | Male | 68 | 160 |
| 1009 | Male | 72 | 160 |
コードを確認してみましょう。クエリにWHERE句が1つ追加されただけです。WHERE句を使用して、一致するID番号を持つ行のみを選択することで、直積全体をサブセット化しています。WHERE句の列名は、同じである必要はありません。最後に、テーブルを実際に確認できるように別のクエリが追加され、出力ウィンドウにデータが表示されます。
プログラムを開いて実行し、ログウィンドウと出力を確認してください。
NOTE: テーブルWORK.DEMO_INFO(行数226、列数4)が作成されました。
ついに、目的の結果を得ることができました。クエリの結果からわかるように、これは各テーブルから2つの列を横に結合したようなものです。SASではこれも結合と呼びます。このケースでは、一致する行のみを選択したため、内部結合とも呼ばれます。このタイプの結合は、データステップのMerge Byと非常に似ていますが、計算リソースとコーディングが少なくて済みます。PROC SQLには、ここで扱った範囲を超えた、その他のタイプの結合と統合結合（行の垂直結合）があります。興味がある方は、これまでの基礎を踏まえて、自分で調べてみてください！