PROC REPORT add a row with column enumaration

mattj — Thu, 03 Nov 2022 21:16:25 GMT

Dear Group,

I would like to add a row just below column headers with individual number. I'd rather do it automatically instead of manually editing title in each column statement - columns number may change. It would very much help me to refer to the columns during the discussion since some of them may have a lengthy description and reuse the numbers in multipage tables.

The example of the desired outcome is attached.

Best regards,

Matthew

Re: PROC REPORT add a row with column enumaration

data_null__ — Fri, 04 Nov 2022 14:38:16 GMT

@mattj wrote:

Dear Group,

I would like to add a row just below column headers with individual number. I'd rather do it automatically instead of manually editing title in each column statement - columns number may change. It would very much help me to refer to the columns during the discussion since some of them may have a lengthy description and reuse the numbers in multipage tables.

The example of the desired outcome is attached.

Best regards,

Matthew

I do something similar where the first header row is variable LABEL and the second row is the variable name. That format is useful when making EXCEL output that you might want to read back into SAS. Here are examples that might be helpful.

%let vars=Cylinders Horsepower MPG_City MPG_Highway Weight Wheelbase Length Make Model Type Origin DriveTrain MSRP Invoice EngineSize;
options formchar="|----|+|---+=|-/\<>*";
proc report data=sashelp.cars(obs=10) nowd list headline;
   column %m_expand_varlist(data=sashelp.cars,var=&vars,expr=catx(' ','(',quote(strip(coalesceC(_label_,_name_))),quote('--'),_name_,')'));
   label %m_expand_varlist(data=sashelp.cars,var=&vars,expr=catx(' ',_name_,'=',quote(cats('(',_index_,')'))));
   define _all_ / display;
   run;


proc report data=sashelp.cars(obs=10) nowd list headline;
   column %m_expand_varlist(data=sashelp.cars,var=&vars,expr=catx(' ','(',quote(strip(coalesceC(_label_,_name_))),quote('--'),_name_,')'));
   attrib _all_ label=' ';
   define _all_ / display;
   run;


proc report data=sashelp.cars(obs=10) nowd list headline;
   column %m_expand_varlist(data=sashelp.cars,var=&vars,expr=catx(' ','(',quote(cats('(',_index_,')')),quote('--'),_name_,')'));
   /*label %m_expand_varlist(data=sashelp.cars,var=&vars,expr=catx(' ',_name_,'=',quote(cats('(',_index_,')'))));*/
   attrib _all_ label=' ';
   define _all_ / display;
   run;

The macro M_EXPAND_VARLIST

%macro
   m_expand_varlist /*Returns an expanded variable list and optionally creates an indexed data set of variable names*/
      (
         data  = _LAST_,            /*[R]Input data*/
         var   = _ALL_,             /*[R]Variable List expanded*/
         copy  = &var,              /*[O]Copy &VARS to the OUT= data set*/
         where = 1,                 /*[R]Where clause to subset OUT=, useful for selecting by a name suffix e.g. where=_name_ like '%_Status'*/
         expr  = nliteral(&name),   /*[R]An expression that can be used to modify the names in the expanded list*/
         keep  = ,                  /*[O]Keep data set option for DATA=*/
         drop  = ,                  /*[O]Drop data set option for DATA=*/
         rename= ,                  /*[O]Rename data set option for DATA=*/
         out   = work._deleteme_,   /*[O]Output data indexed by _NAME_ and _INDEX_*/
         name  = _NAME_,            /*[R]Name of the variable name variable in the output data set*/
         label = _LABEL_,           /*[R]Name of the variable label variable in the output data set*/
         index = _INDEX_,           /*[R]Name of the variable index variable in the output data set*/
         dlm   = ' '                /*[R]List delimiter*/
      );
   %local m i;
   %let i=&sysindex;
   %let m=&sysmacroname._&i;
   %do %while(%symexist(&m));
      %let i = %eval(&i + 1);
      %let m=&sysmacroname._&i;
      %end;
   /*%put NOTE: &=m is a unique symbol name;*/
   %local rc &m code1 code2 code3 code4 code5;
   %let code1 = %str(options notes=0; proc transpose name=&name label=&label data=&data(obs=0 keep=&keep drop=&drop rename=(&rename)) out=&out(where=(&where)); var &var; copy &copy; run;);
   %let code2 = %str(data &out(index=(&index &name)); set &out; &index+1; run;);
   %let code3 = %str(proc sql noprint; select &expr into :&m separated by &dlm from &out; quit;);
   %if %superq(OUT) eq %str(work._deleteme_) %then %let code4=%str(proc delete data=work._deleteme_; run;);
   %let code5 = %str(options notes=1;);
   %let rc=%sysfunc(dosubl(&code1 &code2 &code3 &code4 &code5));
&&&m.
   %mend m_expand_varlist;

Re: PROC REPORT add a row with column enumaration

Ksharp — Sat, 05 Nov 2022 10:33:18 GMT

data have;
 set sashelp.class;
run;


proc sql noprint;
select cats(name,'="(',varnum,')"'),catx(' ','("',name,'"',name,')')
 into :labels separated by ' ',:headers separated by ' '
 from dictionary.columns
  where libname='WORK' and memname='HAVE';
quit;

ods rtf file='c:\temp\temp.rtf' style=minimal;
proc report data=have nowd;
label &labels.;
column &headers.;
run;
ods rtf close;

topic Re: PROC REPORT add a row with column enumaration in ODS and Base Reporting

PROC REPORT add a row with column enumaration

Re: PROC REPORT add a row with column enumaration

Re: PROC REPORT add a row with column enumaration