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;

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;



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;





「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をセルに出力することが出来ました!
ただし、気になるところが。。

1シートに複数の表を出力した場合、各表毎にTITLEとFOOTNOTEが表示されちゃってます。。




そこで、以下のオプションを指定すると、1シートに複数の表があっても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 _char_ / style(column)={tagattr='type:String format:@'};
    run;

ods excel close;


上の例で、SAS側で余白を設定するときの単位はインチだけど、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 _char_ / style(column)={tagattr='type:String format:@'};
    run;

ods excel close;






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 _char_ / style(column)={tagattr='type:String format:@'};
    run;

ods excel close;


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