PROC REPORT variable names that begin and end with an underscore.

[data_null__]

Below you will notice that _TYPE_ and _FREQ_ have lost the underscore.  I know that PROC REPORT has special processing for header strings that begin and end with dash, underscore and some others I don't remember.  Can I get PROC REPORT to NOT do that.

Update: 

Note: The use of expanding characters is supported only in LISTING destinations. Therefore, PROC REPORT simply removes the expanding characters 
when the output is directed to any other destination. Refer to Understanding ODS Destinations in SAS Output Delivery System: 
User’s Guide for more information.

I guess the answer is NO.

proc summary data=sashelp.class missing chartype descendtypes;;
   class age sex name / mlf;
   ways 1;
   output out=test;
   run;

ods excel file='.\test.xlsx';

proc report data=test;
   columns _all_;
   define _all_ / display;
   format _type_ $char3.;
   run;

ods excel close;

[Ksharp]

John King,

Yeah. In old destionation LISTING, sas would print _FREQ_ as  __________FREQ___________ .

But I don't understand since yours is not LISTING , why sas would keep this weird behavior.

Anyway, a workaround way is renaming it ,but that is really clumsy . You like it ?

proc summary data=sashelp.class missing chartype descendtypes;;
   class age sex name / mlf;
   ways 1;
   output out=test;
   run;


options validvarname=any;
ods _all_ close;
ods excel file='c:\temp\test.xlsx';

proc report data=test(rename=(_freq_=' _FREQ_ 'n)) nowd;
   columns _all_;
   define _all_ / display ;
   format _type_ $char3.;
   run;

ods excel close;

[ballardw · Accepted solution]

Or assign a label with extra underscores :

proc report data=test nowd;
   columns _all_;
   define _all_ / display ;
   format _type_ $char3.;
   label _freq_='__freq__' _type_='__type__';
run;

[data_null__]

The suggestion by @ballardw to modify the variable label is the most effective work around, renaming is as suggested by @Ksharp works as long as the variable name is LE 30 characters.

I implemented modifying the label using M_EXPAND_VARLIST.

817        %let label = %m_expand_varlist(data=test,where=(_name_ like '\_%\_' escape '\'),expr=catx('=',nliteral(_name_),quote(cats('_',_name_,'_'))));
818        %put NOTE: &=label;
NOTE: LABEL=_TYPE_="__TYPE__" _FREQ_="__FREQ__"
819        
820        ods excel file='.\test.xlsx';
821        ods listing close;
822        proc report data=test list;
823           columns _all_;
824           define _all_ / display;
825           attrib _all_ label=' ';
826           label &label;
827           run;
NOTE: Multiple concurrent threads will be used to summarize data.
PROC REPORT DATA=WORK.TEST LS=256 PS=60  SPLIT="/" NOCENTER ;
COLUMN  ( Age Sex Name _TYPE_ _FREQ_ );
 
DEFINE  Age / DISPLAY FORMAT= $12. WIDTH=12    SPACING=2   LEFT "Age" ;
DEFINE  Sex / DISPLAY FORMAT= $1. WIDTH=1     SPACING=2   LEFT "Sex" ;
DEFINE  Name / DISPLAY FORMAT= $8. WIDTH=8     SPACING=2   LEFT "Name" ;
DEFINE  _TYPE_ / DISPLAY FORMAT= $3. WIDTH=3     SPACING=2   LEFT "__TYPE__" ;
DEFINE  _FREQ_ / DISPLAY FORMAT= BEST9. WIDTH=9     SPACING=2   RIGHT "__FREQ__" ;
RUN;

%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 © 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;

[Kathryn_SAS]

Here is the documentation that describes the behavior you are seeing:

In LISTING output, if the first and last characters of a heading are one of the following characters, then PROC REPORT uses that character to expand the heading to fill the space over the column or columns. Note that the <> and the >< must be paired. − = . _ * + <> ><

Note: The use of expanding characters is supported only in LISTING destinations. Therefore, PROC REPORT simply removes the expanding characters when the output is directed to any other destination. Refer to Understanding ODS Destinations in SAS Output Delivery System: User’s Guide for more information.

In your example, you could use PROC PRINT since all variables are defined as Display.