You have a mixture of tabs and spaces which causes the inconsistent alignment.  I like to set my editor to insert spaces for tabs and try to convert any tabs that may be copied from external sources, to spaces.  I like tab stops every 3 spaces.     The picture below is your file viewed in ultra-edit with view options to show tabs, spaces, and line endings.    ... View more

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; ... View more

I hadn't tested that change. 🙂 ... View more

Hi @yabwon    Yes, this is good idea.   You could use SAS Help Center: %SYSMEXECNAME Macro Function to retrieve the name of the calling macro.   But I think all we need to know is the depth of the call SAS Help Center: %SYSMEXECDEPTH Macro Function and not have to worry about the length of the caller's name.  %macro u_isnull(macvar); %if %symexist(&macvar.) %then %sysevalf(%superq(%superq(macvar)) =, boolean); %else 1 %mend u_isnull; %macro u_optstore(arg); %put NOTE: %nrstr(%%)sysmexecdepth=%sysmexecdepth; %put NOTE: &=depth; %local /readonly depth=%sysmexecdepth; %if %qupcase(&arg) eq SAVE or %u_isnull(arg) %then %do; proc optsave out=__optbase_&depth.__; run; %end; %else %do; proc optsave out=__optcomp_&depth.__; run; proc compare base=__optbase_&depth.__ comp=__optcomp_&depth.__ out=__optload_&depth.__ outbase outnoequal noprint; run; proc print; run; proc optload data=__optload_&depth.__; run; proc delete data=__optbase_&depth.__ __optcomp_&depth.__ __optload_&depth.__; run; %end; %mend u_optstore; %put %nrstr(%%)sysmexecdepth=%sysmexecdepth; %u_optstore(save) %macro macro2(); %put %nrstr(%%)sysmexecdepth=%sysmexecdepth; %put B; %u_optstore(SAVE) %put BB; %u_optstore(LOAD) %put BBB; %mend macro2; %macro macro1(); %put %nrstr(%%)sysmexecdepth=%sysmexecdepth; %put A; %u_optstore(SAVE) %put AA; %macro2() %put AAA; %u_optstore(LOAD) %put AAAA; %mend macro1; %macro1() %u_optstore(LOAD);   ... View more

If you add the MLF option to the CLASS statement SAS will convert numeric variables (AGE) to character variables.   This makes it easier to work with &VARLIST in the OUTPUT data.   Can even make ARRAY _V &VARLIST;    Also, DESCENDTYPES will output data in VARLIST order.   proc summary data=sashelp.class chartype DESCENDTYPES; class &varlist. / missing MLF; types () &varlist. ; output out=summary; run;   ... View more

Can you elaborate on how much performance overhead you anticipate.    Are you going to call this macro thousands of times.  From your description (this is to be part of a macro that writes partial data step statements) it seems to me that a few calls to DOSUBL to build the data step code would have acceptable performance.   (ii) dump the clauses into a side-session view via %sysfunc(dosubl) to then process that, but that causes quite some performance overhead   ... View more

You could use two calls to proc transpose and is pretty easy to extend to more variables.   data have; infile cards dsd dlm='09'x; input (Locker_ID Locations Person_A Person_B Person_C Person_A_Code Person_B_Code Person_C_Code)(:$32.); cards; 101 Hampersmith, Silvershine Sam Copewell Amy Baker John Aston 123A 234B 345C 102 Coleridge, Brisbake Claude Tremaine Marissa Carter 567D 678E 103 Waterloop, Sturrey Brian Amble Charlie Springwater Mandy Smith 012G 134H 245J ;;;; run; proc transpose data=have out=flip(where=(not missing(col1))) name=vname; by locker_id locations; var person:; run; proc sort data=flip; by locker_id locations vname; run; data flip; set flip; length _name_ $32; pid = first(scan(vname,2,'_')); if find(vname,'code','IT') then _name_ = 'Code'; else _name_ = scan(vname,1,'_'); run; proc transpose data=flip out=flop(drop=_name_); by locker_id locations pid; var col1; id _name_; run; ... View more

@Jan_Jensen sorry I did not notice that you posted this for Visual Analytics.  I do not know or use VA and cannot help. ... View more