/**************
●10_1. tbl_break_init・・・列幅・行幅調整（自動／固定）初期設定
《使い方》
tbl_break_init;
*********************/
%macro tbl_break_init;
data tbl_break_max_cnt;
word_break_cnt=.;
row_num=.;
flg=.;
run;
%mend tbl_break_init;
/**************
●10_1_1. modify_rowheight_nobreak・・・列幅・行幅調整（計算）改行なし
temp_file_nm :ひな形のブック名
sht_nm:作成帳票のシート名
row_title:シートのタイトル行数
《%sht_prm実行で作成》
row_max:データ終了行
《引数》
ds:出力対象のデータセット
var_name:出力項目
range:入力元のセルのアドレス
《使い方》
%modify_rowheight_nobreak(ds=testdt,var_name=col1-col10,length 100,range=r1c1:r65536c10);
*********************/
%macro modify_rowheight_nobreak(range=,word=);
filename exc dde "excel|[&temp_file_nm..xlsx]&sht_nm.!&range.";
data _null_;
set &ds;
infile exc dlm="09"x dsd lrecl=50000 notab;
input &var_nm.;
run;
%mend sht_output_data;
/**************
●10_1_1. modify_rowheight_nobreak・・・列幅・行幅（計算）改行なしの場合
《必須グローバル変数》
ds:出力対象のデータセット
《必須変数》row_num
《%sht_prm実行で作成》
row_max:データ終了行
row_start_data:データ開始行
《引数》
mod_col_varname:対象の変数名（データセットの）
mod_col_column:対象列番号（エクセルの）：例）4
mod_colwidth:列幅 例）28
font_size フォントサイズ：例）10
《使い方》
%modify_rowheight_nobreak(mod_col_varname=tre_hit_2,mod_col_colun=8,mod_col_width=20,font_size=10);
*********************/
%macro modify_rowheight_nobreak(mod_col_varname=,mod_col_colum=,mod_col_width=,font_size=);
data &ds.outds_wk;
set &ds._outds;
word_break_cng=int(length(&mod_col_varname.)/&mod_col_width.*10/&font_size.))+1;
run;
data tbl_break_max_cnt;
merge
&ds._outds_wk(in=x rename=word_break_cnt=var1)
tbl_break_max_cnt(in=y rename=word_break_cnt=var2 drop=flg)
;by row_num;
if x;
if var1>var2 then do;
word_break_cnt=var1;
flg=1;
end;
else do;
word_break_cnt=var2;
end;
keep row_num word_break_cnt flg;
run;
%let statement=;
%let times=%sysfunc(ceil(&nobs./100));
%do i=1 %to ×
proc sql noprint;
select
cats("put'[select(""r",sum(&row_start_data.,row_num,-1)
,"r",sum(&row_start_data.,row_num,-1),""")]';"
,"put '[row.height(",f0.word_break_cnt*13+7,")]';")
into
:statement separeted by " "
from &ds_outds_wk f0
where flg=1
and f0.row_num>(&i.-1)*100
and f0.row_num<=(&i.)*100
;
quit;
*%put %str("&statement.");*test;
filename exc_sys dde 'excel|system';
data _null_;
file exc_sys;
put '[select("'"&r&row_start_data.c&mod_col_column.:r&row_max.c&mod_col_column."'")]';
put "[column.width(&mod_col_width.)]";
put '[alignment(,true)]';
put '[select("'"&r&row_start_data.c1"'")]';
run;
%end;
%mend modify_rowheight_nobreak;
/**************
●10_1_2. modify_rowheight_break・・・列幅・行幅（計算）改行ありの場合
《必須グローバル変数》
ds:出力対象のデータセット
《必須変数》row_num
《%sht_prm実行で作成》
row_max:データ終了行
row_start_data:データ開始行
《引数》
mod_col_varname:対象の変数名（データセットの）
mod_col_column:対象列番号（エクセルの）：例）4
mod_colwidth:列幅 例）28
font_size フォントサイズ：例）10
《使い方》
%modify_rowheight_break(mod_col_varname=tre_hit_2,mod_col_column=23);
*********************/
%macro modify_rowheight_break(mod_col_varname=,mod_col_column=);
data &ds.outds_wk;
set &ds._outds;
word_break_cnt=count(&mod_col_varname.,"0d0a"x)+1;
run;
data tbl_break_max_cnt;
merge
&ds._outds_wk(in=x rename=word_break_cnt=var1)
tbl_break_max_cnt(in=y rename=word_break_cnt=var2 drop=flg)
;by row_num;
if x;
if var1>var2 then do;
word_break_cnt=var1;
flg=1;
end;
else do;
word_break_cnt=var2;
end;
keep row_num word_break_cnt flg;
run;
%let statement=;
%let times=%sysfunc(ceil(&nobs./100));
%do i=1 %to ×
proc sql noprint;
select
cats("put'[select(""r",sum(&row_start_data.,f0.row_num,-1)
,"r",sum(&row_start_data.,f0.row_num,-1),""")]';"
,"put '[row.height(",f0.word_break_cnt*13+7,")]';")
into
:statement separeted by " "
from &ds_outds_wk f0
where flg=1
and f0.row_num>(&i.-1)*100
and f0.row_num<=(&i.)*100
;
quit;
*%put %str("&statement.");*test;
filename exc_sys dde 'excel|system';
data _null_;
file exc_sys;
put '[select("'"&r&row_start_data.c&mod_col_column.:r&row_max.c&mod_col_column."'")]';
put '[column.width(100)]';
put '[column.width(,,true,3)]';
put '[select("'"&r&row_start_data.c1"'")]';
run;
%end;
%mend modify_rowheight_break;
/**************
●10_1_3. page_break_calc・・・pagesizeをもとに行の高さを計算し、改ページを挿入
《必須グローバル変数》
ds:出力対象のデータセット
《必須変数》row_num
《%sht_prm実行で作成》
row_start_data:データ開始行
《引数》
page_size:1ページの行数
《使い方》
%page_break_calc(mod_col_varname=tre_hit_2,mod_col_column=23);
*********************/
%macro page_break_calc(mod_col_varname=,mod_col_column=);
data _null_;
call symput("sort2",tranwrd("&sort.",","," "));
run;
proc sort data=&ds.outds;by row_num;run;
data tbl_break_max_cnt2;
merge
&ds._outds(in=x)
tbl_break_max_cnt
;
if word_break_cnt=. then word_break_cnt=1;
run;
proc sql;
create table tbl_break_max_cnt3 as
select
*
,sum(word_break_cnt) as adr_row_cnt
from tbl_break_max_cnt2
group by &group.
orer by &sort.
;
quit;
data tbl_greak_max_cnt4;
set tbl_break_cnt3;
retain xum_xord_break_cnt_r 0 word_break_cnt_r 1;
wor_break_cnt_r=word_break_cnt;
cum_word_break_cnt_r=cum_word_break_cnt;
run;
proc sort data=tbl_break_max_cnt4;by &sort2;run;
data tbl_break_max_cnt5;
set tbl_break_max_cnt4;
by &sort2;
retain pre_end_no end_no cum_row page_no 0;
if first.&group. then do;
end_no=sum(cum_word_greak_cnt,adr_row_cnt,-1);
end;
a=cum_row+15;
rif page_no=0 then page=%eval(&pagesize.-&row_title.-&row_header.);
else page=%eval(&pagesize.-&row_header.);
if end_no >= cum_row+page then do;
if first.&group. then do;
page_no=page_no+1;
cum_row=pre_end_no;
end;
end;
pre_end_no=end_no;
run;
proc sort data=tbl_break_max_cnt5;by page_no &sort2;run;
data &ds.outds;
set tbl_break_max_cnt5;
by page_no &sort2.;
if _n_=1 and first.page_no then page_break_flg=1;
run;
%let statement=;
proc sql noprint;
select
cats("put'[select(""r",sum(&row_title.,&row_header.,row_num)
,"r",sum(&row_title.,&row_header.,row_num),""")]';"
,"put '[set.page.greak]';")
into
:statement separeted by " "
from &ds_outds
where flg=1
;
quit;
*%put %str("&statement.");*test;
filename exc_sys dde 'excel|system';
data _null_;
&statement.;
run;
%end;
%mend page_break_calc;
/**************
●12_1. cond_data_put_statement・・・条件付き命令（データ依存の条件）;
《必須グローバル変数》
row_title:シートのタイトル行数
row_header:項目行数
ds:出力対象のデータセット
《引数》
col_start 開始列 例）4、&row_start_data、%eval(&row_title+1)
col_end 終了列 例）28、&row_max、%eval(&row_max.+1)
cond 条件式 例）id^=""
put_statement 実行したいput分
例）put '[border(2,1,1,1,1)]'、put '[patterns(1,1,36,true)]'
《使い方》
%cond_data_put_statement(col_start=1,col_end=&col_max.,cond=mod(row_num,2)=1,put_statement='[patterns(1,1,36,true)]');
%cond_data_put_statement(col_start=1,col_end=1,cond=sex="女子",put_statement=%bquote('[font.properties("ＭＳ 明朝","bold",12,1,1,1,1)]');
*********************/
%macro cond_data_put_statement(col_start=,col_end=,cond=,put_statement=);
%let statement=;
%let times=%sysfunc(ceil(&nobs./100));
%do i=1 %to ×.;
proc sql noprint;;
select
cats("put'[select(""r",sum(&row_title.,&row_header.,row_num)
,"c&col_start.:r",sum(&row_title.,&row_header.,row_num),"c&col_end.",""")]';"
,"put &put_statement.;")
into
:statement separeted by " "
from &ds_outds f0
where &cond.
and f0.row_num>(&i.-1)*100
and f0.row_num<=(&i.)*100
;
quit;
*%put %str("&statement.");*test;
filename exc_sys dde 'excel|system';
data _null_;
&statement.;
run;
%end;
%mend cond_data_put_statement;
/**************
●12_2. cond_system_put_statement・・・条件付き命令（グローバル変数の条件）;
《%sht_prm実行で作成》
nobs:dsで設定されたデータセットのオブザベーション数
row_max:項目行数
《引数》
col_start 開始列 例）4、&row_start_data、%eval(&row_title+1)
col_end 終了列 例）28、&row_max、%eval(&row_max.+1)
cond 条件式 例）id^=""
put_statement 実行したいput分
例）put '[border(2,1,1,1,1)]'、put '[patterns(1,1,36,true)]'
《使い方》
%cond_system_put_statement(range=r&row_max.c1,cond=&nobs.=0,put_statement='[alignment(2,false,3,0,false)]');
%cond_system_put_statement(range=r&row_max.c1,cond=&nobs.=0,put_statement='[formula("こんにちは")]');
*********************/
%macro cond_system_put_statement(range=,cond=,put_statement=);
%if &cond. %then %do;
filename exc_sys dde 'excel|system';
data _null_;
put '[select("'"&range."'")]';
&statement.;
run;
%end;
%mend cond_system_put_statement;
/**************
●12_3. cond_cell_merge・・・条件付き命令（セルの結合）;
《必須グローバル変数》
row_title:シートのタイトル行数
row_header:項目行数
ds:出力対象のデータセット
《引数》
col_start 開始列 例）4、&row_start_data、%eval(&row_title+1)
col_end 終了列 例）28、&row_max、%eval(&row_max.+1)
cond 条件式 例）id^=""
put_statement 実行したいput分
《使い方》
%cond_cell_merge(col_start=1,col_end=&col_max.,cond=soc_name^="");
*********************/
%macro cond_cell_merge(col_start=,col_end=,cond=);
%let statement=;
%let times=%sysfunc(ceil(&nobs./100));
%do i=1 %to ×.;
proc sql noprint;
select
cats("put'[select(""r",sum(&row_title.,&row_header.,row_num)
,"c&col_start.:r",sum(&row_title.,&row_header.,row_num),"c&col_end.",""")]';"
,"put '[alignment(,,,,,,,&merge.)]';")
into
:statement separeted by " "
from &ds_outds f0
where &cond.
and f0.row_num>(&i.-1)*100
and f0.row_num<=(&i.)*100
;
quit;
*%put %str("&statement.");*test;
filename exc_sys dde 'excel|system';
data _null_;
&statement.;
run;
%end;
%mend cond_cell_merge;