SAS忘備録: 5月 2019

2019年5月31日金曜日

ODS EXCEL入門: 行と列の設定

今回は以下のオプションをご紹介します。

HIDDEN_COLUMNS
HIDDEN_ROWS
ABSOLUTE_ROW_HEIGHT
ABSOLUTE_COLUMN_WIDTH
ROW_HEIGHTS

行と列を非表示にする

構文

ODS EXCEL OPTIONS(
HIDDEN_ROWS = "非表示にする行"
HIDDEN_COLUMNS = "非表示にする列"
);

例

ods excel file="出力するパスを指定\test1.xlsx" options(
hidden_rows = "1-3,5"
hidden_columns = "1,2" );

proc report data=sashelp.class;
run;

ods excel close;

hidden_rows = "1-3,5"

1～3、5行目を非表示にしています。

hidden_columns = "1,2"

1、2列目を非表示にしています。

行の高さと列の幅

構文

ODS EXCEL OPTIONS(
ABSOLUTE_ROW_HEIGHT = "行の高さ"
ABSOLUTE_COLUMN_WIDTH = "列の幅"
);

例

ods excel file="出力するパスを指定\test2.xlsx" options(
absolute_row_height = "0.5in"

absolute_column_width = "0.5in, 1in" );

proc report data=sashelp.class;
run;

ods excel close;

absolute_row_height = "0.5in"

全ての行の高さを0.5インチに設定。

absolute_column_width = "0.5in,1in"

1列目「0.5インチ」、2列目「1インチ」、
3列目「0.5インチ」、4列目「1インチ」、
5列目「0.5インチ」、、、

...と繰り返すように列幅を設定。

ちなみに高さを指定したくない行や、幅を指定したくない列がある場合は「0」を指定する。

たとえば「absolute_row_height = "0.5in,0"」みたいに設定すると、1, 3, 5, 7...行目を「0.5インチ」に設定していることになる。

行の種類ごとに高さを設定する

以下のようにタイトルの行とか、BYLINEの行とか、行の種類毎に高さを設定することができます。

構文

ODS EXCEL OPTIONS(  ROW_HEIGHTS = "table header の高さ ,
table body の高さ ,
     BY lines の高さ ,
       titles の高さ ,
     footers の高さ ,
     page break の高さ ,
     paragraph skip の高さ"
);

上の構文の「page break」が何の高さなのか、よく分からんです。改ページの高さって？？分かる方教えて。。
「paragraph skip」ってのは、各段落（タイトル行とかBY値の行とか）の間に空白行がありますよね？その空白行のことです。

例

title "MyTitle";

ods excel file="出力するパスを指定\test3.xlsx" options(
embedded_titles = "on"
row_heights = "0.5in, 0.3in, 0, 0, 0, 0, 0.1in" );

proc report data=sashelp.class;
run;

ods excel close;

row_heights = "0.5in, 0.3in, 0, 0, 0, 0, 0.1in"

表のヘッダー「0.5インチ」
表の中身「0.3インチ」
段落の間の空白行「0.1インチ」

...というように行の要素ごとに高さを設定しています。

設定しない要素には「0」を入れておきます。

ODS EXCEL入門記事一覧

注意点

2019年5月27日月曜日

ODS EXCEL入門: 改ページの設定

ODS EXCELで改ページを設定する以下のオプションをご紹介。

ROWBREAKS_COUNT
ROWBREAKS_INTERVAL

ROWBREAKS_COUNT … 何行毎に改ページを入れるか設定

構文

ODS EXCEL OPTIONS( ROWBREAKS_COUNT = "行数" );

例

ods excel file="出力するパスを指定\test1.xlsx" options( rowbreaks_count = "3" );

proc report data=sashelp.class;
run;

ods excel close;

3行毎に改ページを設定してます。1行目の項目名のとこはノーカウント。

ROWBREAKS_INTERVAL … 改ページの入れ方

構文

ODS EXCEL OPTIONS( ROWBREAKS_INTERVAL = "改ページの入れ方" );

改ページの入れ方は以下が設定できます。

指定	結果
OUTPUT	出力オブジェクト毎に改ページを入れる
PROC	プロシジャ毎に改ページを入れる
NONE	改ページを設定しない

1個目の「出力オブジェクト」って何？って方は以下「ODS TRACE」を理解すると、なんとなくわかるかと思います。
http://sas-boubi.blogspot.com/2015/07/ods-traceods-output.html

例

ods excel file="出力するパスを指定\test2.xlsx" options(
sheet_interval="none"
rowbreaks_interval= "output" );

proc univariate data=sashelp.class;
run;

ods excel close;

「rowbreaks_interval = "output"」で出力オブジェクト毎に改ページを入れています。

ODS EXCEL入門記事一覧

注意点

2019年5月23日木曜日

ODS EXCEL入門: EXCELの「ページ設定」の「シート」タブの内容を設定する

EXCELの「ページ設定」→「シート」タブの内容をODS EXCEL側で設定する方法をご紹介。

以下ODS EXCELで「シート」タブの内容を設定する構文。上のと見比べて、どう対応してるか確認してみてください。

構文

ODS EXCEL OPTIONS(
PRINT_AREA = "セル" /* 印刷範囲 */
ROW_REPEAT = "行" /* タイトル行 */
COLUMN_REPEAT = "列" /* タイトル列 */
GRIDLINES = "ON" /* 印刷 : 枠線 */
BLACKANDWHITE = "ON" /* 印刷 : 白黒印刷 */
DRAFTQUALITY = "ON" /* 印刷 : 簡易印刷 */
ROWCOLHEADINGS = "ON" /* 印刷 : 行列番号 */
PAGE_ORDER_ACROSS = "ON" /* ページの方向 : 上から下 */
);

例

ods excel file="出力するパスを指定\test.xlsx" options(
print_area = "A1:G9"
row_repeat = "1-3"
column_repeat = "1-2"
gridlines = "on"
page_order_across = 'on'
);

proc means data=sashelp.class;
var height;
class sex;
run;

ods excel close;

ODS EXCEL入門記事一覧

注意点

2019年5月22日水曜日

ODS EXCEL入門: EXCELの「ページ設定」の「ヘッダー/フッター」タブの内容を設定する

今回は以下のオプションについて解説しています。

PRINT_HEADER
PRINT_FOOTER
EMBEDDED_TITLES
EMBEDDED_FOOTNOTES
EMBED_TITLES_ONCE
EMBED_FOOTNOTES_ONCE

EXCELの「ページ設定」→「ヘッダー/フッター」タブで、ヘッダーとフッターを設定できるけど、それをODS EXCEL側で設定する方法をご紹介。

ヘッダー/フッターの設定

構文

ODS EXCEL OPTIONS(
PRINT_HEADER = "ヘッダーテキスト"
PRINT_FOOTER = "フッターテキスト"

);

例

title;
footnote;

ods excel file="出力するパスを指定\test.xlsx" options(
print_header = "myへっだー"
print_footer = "myふったー");

proc means data=sashelp.class;
var height;
class sex;
run;

ods excel close;

ただし、以下のように「TITLEステートメント」や「FOOTNOTEステートメント」が設定されている場合は、そちらがExcelの「ヘッダー/フッター」として優先されます。

例

title "titleステートメントが優先される";
footnote "footnoteステートメントが優先される";

ods excel file="出力するパスを指定\test2.xlsx" options(
print_header = "myへっだー"
print_footer = "myふったー");

proc means data=sashelp.class;
var height;
class sex;
run;

ods excel close;

ちなみに以下の記事で「ヘッダー・フッター」の小技を紹介しているので、余裕があったら参考までに。。
ODS EXCELで「ヘッダー・フッター」の書式を設定する

「TITLEステートメント」と「FOOTNOTEステートメント」の設定いろいろ

ここから、おまけ。

以下オプションで「TITLEステートメント」や「FOOTNOTEステートメント」の値を「ヘッダー/フッター」ではなく、セルに出力することが出来ます。

構文

ODS EXCEL OPTIONS(
EMBEDDED_TITLES = "ON" | "OFF"
EMBEDDED_FOOTNOTES = "ON" | "OFF"

);

例

title "titleをセルに埋め込む";
footnote "footnoteをセルに埋め込む";

ods excel file="出力するパスを指定\test3.xlsx" options(
sheet_interval = "none"
embedded_titles = 'on'
embedded_footnotes = 'on'
);

proc means data=sashelp.class;
var height;
class sex;
run;

proc means data=sashelp.class;
var height;
class age;
run;

ods excel close;

TITLEとFOOTNOTEをセルに出力することが出来ました！
ただし、気になるところが。。

上の例では、MEANSプロシジャを2回実行して2つの表を出力していますが、各表毎にTITLEとFOOTNOTEが表示されちゃってます。。

そこで、以下のオプションを指定すると、TITLEとFOOTNOTEを一番最初と最後だけに出力するようにできます。

構文

ODS EXCEL OPTIONS(
EMBED_TITLES_ONCE = "ON" | "OFF"
EMBED_FOOTNOTES_ONCE = "ON" | "OFF"

);

例

title "titleをセルに埋め込む";
footnote "footnoteをセルに埋め込む";

ods excel file="出力するパスを指定\test4.xlsx" options(
sheet_interval = "none"
embedded_titles = 'on'
embedded_footnotes = 'on'
embed_titles_once = 'on'
embed_footnotes_once = 'on'
);

proc means data=sashelp.class;
var height;
class sex;
run;

proc means data=sashelp.class;
var height;
class age;
run;

ods excel close;

こっちの方がスッキリしてていいですね。

ODS EXCEL入門記事一覧

注意点

2019年5月20日月曜日

ODS EXCEL入門: EXCELの「ページ設定」の「余白」タブの内容を設定する

EXCELの「ページ設定」→「余白」タブの内容をODS EXCEL側で設定する方法。

以下が「余白」タブの内容を設定するODS EXCELの構文。上のと見比べてどう対応してるのか確認してみましょう。

構文

ODS EXCEL OPTIONS(
PRINT_HEADER_MARGIN = "値" /* ヘッダー余白 (inch) */
PRINT_FOOTER_MARGIN = "値" /* フッター余白 (inch) */

CENTER_HORIZONTAL = "ON" /* ページ中央 (水平) */

CENTER_VERTICAL = "ON" /* ページ中央 (垂直) */

);

ヘッダー・フッター以外の余白を設定するオプションがODS EXCELに無いのですが、以下の記事で紹介しているシステムオプションで設定出来ます。

ODS出力時の用紙サイズや余白などを設定するオプション [まとめ]

例

options papersize=Letter topmargin=2in bottommargin=2in leftmargin=2in rightmargin=2in;

ods excel file="出力するパスを指定\test.xlsx" options(
flow="table"
orientation = 'landscape'
print_header_margin = "1"
print_footer_margin = "1"
center_horizontal = "on"
center_vertical = "on");

proc report data=sashelp.cars;
define _character_ / style(column)={tagattr='type:String format:@'};
run;

ods excel close;

上の例で、SAS側で余白を設定するときの単位はインチだけど、Excel側に反映されるときは（日本だと）センチメートルに変換されるんで、そこんとこ気をつけてください。

ODS EXCEL入門記事一覧

注意点

2019年5月16日木曜日

SUBSTR vs SUBSTRN

以下記事で「SUBSTRN関数」を紹介しました。
「文字列を抽出するSUBSTRN関数」

同じような機能で「SUBSTR関数」ってのもあり、皆さんはよくこちらを使うと思います。
では「SUBSTR関数」と「SUBSTRN関数」がどう違うのか、比較してみたいと思います。

1. 「SUBSTR」にしかない機能

指定した位置の文字を置換する事ができる（むかし書いた記事「SUBSTR関数の小技」でも紹介）

data test;
length x $3.;
x = "abc";
substr( x, 2, 1 ) = "X";
run;

2. 「SUBSTRN」にしかない機能

第1引数に数値変数を指定した場合、自動でBEST32形式の文字値に変換されて、抽出が行われる。

data test2;
length x 8. y $10.;
x = 123;
y = substrn( x, 2, 2 );
run;

ちなみに数値を文字値に変換すると「 123」みたいに前方に余計な半角スペースが入ってしまう事があるけど、SUBSTRNは文字値に変換する時にこの余計な半角スペースも取り除いてくれます。

3. 変数のLENGTHを越える位置の文字抽出

/* SUBSTR */
data test;
length x $3. y1 y2 $10.;
x = "abc";
y1 = substr( x, 1, 5 );
y2 = substr( x, 5, 1 );
run;

/* SUBSTRN */
data test2;
length x $3. y1 y2 $10.;
x = "abc";
y1 = substrn( x, 1, 5 );
y2 = substrn( x, 5, 1 );
run;

上の例では、変数Xのlengthが「3」なのに対して、「substr(x,1,5)」や「substr(x,5,1)」みたいにlengthを飛び越える位置の文字を抽出しようとしています。

「SUBSTR」も「SUBSTRN」も結果は一緒ですが、

「SUBSTR」はログに「NOTE: 関数SUBSTR(行 xx カラム x)の第x引数は無効です。」と出ます。
「SUBSTRN」はこのNOTEメッセージが出ません。

4. 抽出開始位置に0以下を指定した場合

/* SUBSTR */
data test3;
length x $3. y $10.;
x = "abc";
y = substr( x, -1, 4 );
run;

/* SUBSTRN */
data test4;
length x $3. y $10.;
x = "abc";
y = substrn( x, -1, 4 );
run;

上の例では、「substr(x,-1,4)」というように抽出を開始する位置にマイナスの値を指定しています。

「SUBSTR」はマイナスの位置を認識できず、ログに「NOTE: 関数SUBSTR(行 xx カラム x)の第2引数は無効です。」と出て第2引数が無効となり、結果は欠損値になります。
「SUBSTRN」は以下のようにマイナス方向の位置も認識できるようです。

「-1」の位置から「4バイト」を抽出するんで「ab」が返されるわけですね。

5. 抽出する長さに0以下を指定した場合

/* SUBSTR */
data test5;
length x $3. y $10.;
x = "abc";
y = substr( x, 1, -1 );
run;

/* SUBSTRN */
data test6;
length x $3. y $10.;
x = "abc";
y = substrn( x, 1, -1 );
run;

上の例では、「substr(x,1,-1)」というように抽出する長さにマイナスの値を指定しています。

「SUBSTR」はマイナスの長さを認識できず、ログに「NOTE: 関数SUBSTR(行 xx カラム x)の第3引数は無効です。」と出て第3引数が無効となり、結果は第2引数に指定した抽出開始位置から文字の終わりまでが抽出されます。
「SUBSTRN」もマイナスの長さを認識できず、こちらはログのNOTEメッセージは出ないものの、結果は欠損値になります。

以上、5つに分けて比較してみましたが、特に1～3は覚えとくと便利です。4～5は細かい話なんで余裕あったら覚えとけばいいと思います。

2019年5月13日月曜日

ODS EXCEL入門: EXCELの「ページ設定」の「ページ」タブの内容を設定する

EXCELの「ページ設定」→「ページ」タブの内容をODS EXCEL側で設定する方法。

以下が「ページ」タブの内容を設定するODS EXCELの構文。上のと見比べてどう対応してるのか確認してみましょう。

構文

ODS EXCEL OPTIONS(
ORIENTATION = "PORTRAIT" | "LANDSCAPE" /* 印刷の向き */
SCALE = "値" /* 拡大/縮小 */
FITTOPAGE = "ON" | "OFF" /* 次のページ数に合わせて印刷: 横1×縦1 */
PAGES_FITWIDTH = "値" /* 次のページ数に合わせて印刷: 横 */
PAGES_FITHEIGHT = "値" /* 次のページ数に合わせて印刷: 縦 */
DPI = "値" /* 印刷品質*/
);

用紙サイズを変えるオプションがODS EXCELに無いのですが、「PAPERSIZE=」システムオプションで設定出来ます。

ODS出力時の用紙サイズや余白などを設定するオプション [まとめ]

例

options papersize=Letter;
ods excel file="出力するパスを指定\test2.xlsx" options(
flow="table"
orientation = 'landscape'
pages_fitheight = '5');

proc report data=sashelp.cars;
define _character_ / style(column)={tagattr='type:String format:@'};
run;

ods excel close;

ODS EXCEL入門記事一覧

注意点

2019年5月8日水曜日

ODS EXCEL入門: 「ウィンドウ枠の固定」の設定

ODS EXCELで「ウィンドウ枠の固定」を設定してみます。

構文

ODS EXCEL OPTIONS(
FROZEN_HEADERS = "固定する行"
FROZEN_ROWHEADERS = "固定する列"
);

例

ods excel file="出力するパスを指定\test2.xlsx" options( flow="table" frozen_headers="1" frozen_rowheaders="2");

proc report data=sashelp.cars;
define _character_ / style(column)={tagattr='type:String format:@'};
run;

ods excel close;

上の例では1行目と2列目が固定されています。

ODS EXCEL入門記事一覧

注意点

2019年5月6日月曜日

ODS EXCEL入門: オートフィルタの設定

ODS EXCELでオートフィルタを設定する方法を紹介。

構文

ODS EXCEL OPTIONS( AUTOFILTER = "設定範囲" );

例1

ods excel file="出力するパスを指定\test1.xlsx" options( flow="table" autofilter="all" );

proc report data=sashelp.class;
define _character_ / style(column)={tagattr='type:String format:@'};
run;

ods excel close;

「autofilter = "all"」で全列にオートフィルタを設定できます。

例2

ods excel file="出力するパスを指定\test2.xlsx" options( flow="table" autofilter="2" );

proc report data=sashelp.class;

define _character_ / style(column)={tagattr='type:String format:@'};

run;

ods excel close;

オートフィルタを設定する列を設定しています。

ちなみに「autofilter="B"」みたいな指定も可能です。

例3

ods excel file="出力するパスを指定\test3.xlsx" options( flow="table" autofilter="1-3" );

proc report data=sashelp.class;
define _character_ / style(column)={tagattr='type:String format:@'};
run;

ods excel close;

オートフィルタを設定する列の範囲を設定しています。

ちなみに「autofilter="A:C"」みたいな指定も可能です。

ODS EXCEL入門記事一覧

注意点

2019年5月2日木曜日

【SAS入門】プログラムの作成・実行の流れ

「SAS Studio 3.7」の画面で説明していきます。Windows版SASと共通する部分についても触れていきます。

1. SASプログラムを書く

「コード」タブをクリックするとコードエディタが表示されます。このコードエディタにプログラムを書いていきます。
（Windows版SASでは「エディタ」というタブ）

試しに上の画像のプログラム（proc means なんちゃらかんちゃら）をコードエディタに書いてみましょう（プログラムは半角文字で書いてください）

ここで書いているプログラムの内容を今覚える必要はありませんが、
「SASHELP.CLASS」というSASデータセットに対して、性別毎の身長の基本統計量を求めています。またこの結果を「OUT_STAT」というSASデータセットに出力しています。