sorting all variables alphabetically

[Siddhu]

data sorting;
 input A B D F G I M Z C N L;
 datalines;
 2 3 7 5 4 8 5 8 6 4 2 
 2 4 1 4 2 5 6 8 0 7 5 
 7 5 3 8 9 2 1 4 7 9 6
;
run;

say, I have this data in this fashion. Now, I want to sort the table to display me all the variables alphabetically as, A B C D E F..... Someone please help me how to sort the data this way. Please note there may be so many such variables where you cannot manually write them with retain statement.

[JosvanderVelden]

Something like

data sorting;
    retain A B C D F G I L M N Z;
    set sorting;
run;

could work.

[Siddhu]

Yes, but I have so many such variables that I cannot manually write all of them in retail statement.

[novinosrin]

data sorting;
 input A B D F G I M Z C N L;
 datalines;
 2 3 7 5 4 8 5 8 6 4 2 
 2 4 1 4 2 5 6 8 0 7 5 
 7 5 3 8 9 2 1 4 7 9 6
;
run;

proc transpose data=sorting out=temp;
var _all_;
run;

proc sort data=temp;
by _name_;
run;

proc transpose data=temp out=want(drop=_:);
id _name_;
var col:;
run;

Or use dictionary tables and macrotise var names

[DrAbhijeetSafai]

Its working fantastically! Wonderful!

[FreelanceReinh]

Hi @Siddhu,

You could create the sorted variable list dynamically from DICTIONARY.COLUMNS (as mentioned by novinosrin) and then use it in a RETAIN statement or anywhere else, as shown below:

proc sql;
select name into :varlist separated by ' '
from dictionary.columns 
where libname='WORK' & memname='SORTING'
order by name;
quit;

proc print data=sorting;
var &varlist;
run;

Limitation: Macro variable VARLIST can accommodate up to 65,534 characters. (Create two or more macro variables with ranges of variable names if this is not enough.)

[novinosrin]

And another one, just a variation 

data sorting;
 input A B D F G I M Z C N L;
 datalines;
 2 3 7 5 4 8 5 8 6 4 2 
 2 4 1 4 2 5 6 8 0 7 5 
 7 5 3 8 9 2 1 4 7 9 6
;
run;

proc contents varnum data=sorting out=w(keep=name);
run;
proc transpose data=w out=temp(drop=_name_);
id name;
run;
data want;
if 0 then set temp;
set sorting;
run;

[ballardw]

Please explain the analysis, reporting or use reason that the  variables in the data set must be in a particular order.

There is usually little actual need for ordering variables, and the more variables involved I tend to think less need.

[Astounding]

While you have many good suggestions, here's an issue you need to consider.

Variable names are always stored in upper case. If you have a mix like this:

A b C D

you have to decide what the order should be, and verify that the method you choose gives you the results you want.

[hashman]

@Siddhu:

All the methods you've been offered are way too complex and incomprehensible. Here's a really simple and easily comprehensible method:

data have ;                                                       
 input A B E:$2. D F G H:$3. I M Z C N W:$4. L ;                  
 cards ;                                                          
2 3 E1 7 5 4 HH1 8 5 8 6 4 WWW1 2                                 
2 4 E2 1 4 2 HH2 5 6 8 0 7 WWW2 5                                 
7 5 E3 3 8 9 HH3 2 1 4 7 9 WWW3 6                                 
run ;                                                             
                                                                  
data _null_ ;                                                     
  if 0 then set have ;                                            
  dcl hash ___hv (ordered:"A") ;                                  
  ___hv.definekey ("___vn") ;                                     
  ___hv.definedone () ;                                           
  dcl hiter ___hi ("___hv") ;                                    
  length ___vn $ 32 ;                                             
  do until (cmiss (___vn)) ;                                      
    call vnext (___vn) ;                                          
    if ___vn notin ("___vn","_N_","_ERROR_","") then ___hv.add() ;
  end ;                                                           
  dcl hash ___hd (dataset:"have", multidata:"Y") ;                
  do _n_ = 1 by 1 while (___hi.next() = 0) ;                     
    if _n_ = 1 then ___hd.definekey (___vn) ;                     
    ___hd.definedata (___vn) ;                                    
  end ;                                                           
  ___hd.definedone () ;                                           
  ___hd.output (dataset:"have_vsorted") ;                         
  stop ;                                                          
run ;                                                             

See? Simple: All in a single step without crossing step boundaries even once. Why the triple-underscore prefixes? We don't want the name of the auxiliary variable ___VN and the names of the hash and hash iterator objects to clash with the variable names in HAVE, the assumption being there're no variables in HAVE whose names are prefixed with three underscores. Well, if it's not bulletproof enough, use 5 underscores ... or 7 ... but no more than 30.   

Now one little caveat with this method is that it requires memory to be plentiful enough to swallow the entire HAVE. It can be easily obviated by a simple subterfuge of making the hash code output a data set with 0 observations and then using this "model" data set to create a view where your variables will be ordered as you wish. Note that OBS=0 is the only difference between the hash code below and above:

data have ;                                                                                                                             
 input A B E:$2. D F G H:$3. I M Z C N W:$4. L ;                                                                                        
 cards ;                                                                                                                                
2 3 E1 7 5 4 HH1 8 5 8 6 4 WWW1 2                                                                                                       
2 4 E2 1 4 2 HH2 5 6 8 0 7 WWW2 5                                                                                                       
7 5 E3 3 8 9 HH3 2 1 4 7 9 WWW3 6                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data _null_ ;                                                                                                                           
  if 0 then set have ;                                                                                                                  
  dcl hash ___hv (ordered:"A") ;                                                                                                        
  ___hv.definekey ("___vn") ;                                                                                                           
  ___hv.definedone () ;                                                                                                                 
  dcl hiter ___hi ("___hv") ;                                                                                                           
  length ___vn $ 32 ;                                                                                                                   
  do until (cmiss (___vn)) ;                                                                                                            
    call vnext (___vn) ;                                                                                                                
    if ___vn notin ("___vn","_N_","_ERROR_","") then ___hv.add() ;                                                                      
  end ;                                                                                                                                 
  dcl hash ___hd (dataset:"have(OBS=0)", multidata:"Y") ;                                                                               
  do _n_ = 1 by 1 while (___hi.next() = 0) ;                                                                                            
    if _n_ = 1 then ___hd.definekey (___vn) ;                                                                                           
    ___hd.definedata (___vn) ;                                                                                                          
  end ;                                                                                                                                 
  ___hd.definedone () ;                                                                                                                 
  ___hd.output (dataset:"have_vsorted") ;                                                                                               
  stop ;                                                                                                                                
run ;                                                                                                                                   
                                                                                                                                        
data vhave / view = vhave ;                                                                                                             
  if 0 then set have_vsorted ;                                                                                                          
  set have ;                                                                                                                            
run ;                                                  

Now instead of referencing HAVE, you can reference VHAVE anywhere in the program downstream, and your variables will appear sorted. Plus, this way the hash memory footprint will be close to nil, and no extra data set has to be created.

Kind regards

Paul D.

p.s. Turn your sense of humor on when reading certain parts of this post. 

[yabwon]

Paul,

One more "just for fun", macro wrapper with dosubl(). [I think Roger would like it.]

All the best

Bart

data have;
input d  c $ B A $;
cards;
1 x 2 y
;
run;


%macro ordvar(ds
             ,shell=  /* default is null, temporary dataset name */
             ,ord=    /* ordering, null or "desc" */
             ,clean=1 /* should the shell be droped or not? */
             )/secure;
  %local rc;

  %if %nrbquote(&shell.) = %then %let shell=_%sysfunc(datetime(),hex16.)_;

  %let rc = %sysfunc(dosubl(options nonotes nosource %str(;)
    proc transpose data = &ds(obs=0) out = &shell.t %str(;)
     var _ALL_ %str(;)
    run %str(;)

    proc sql noprint %str(;)
      create table &shell. as
      select _NAME_
      from &shell.t
      order by upcase(_NAME_) &ord. 
      %str(;)
      drop table &shell.t %str(;)
    quit %str(;)

    data _null_ %str(;)
      call execute("proc sql noprint%str(;) create table &shell. as select ") %str(;)
      do until(EOF) %str(;)
        set &shell. end = EOF %str(;)
        call execute( _NAME_ || ifc(0=EOF, ",", "") ) %str(;)
      end %str(;)
      call execute("from &ds.(obs=0)%str(;) quit%str(;)") %str(;)
    stop %str(;)
    run %str(;)

  options notes source %str(;)
  ));

  if 0 then set &shell.(obs=0); 
  %if &clean. = 1 %then 
    %do;
      if _N_ = 1 then call execute("proc sql noprint%str(;) drop table &shell.%str(;) quit%str(;)");
    %end;

%mend ordvar;

options mprint;
data want;
  %ordvar(have)
  set have;
run;

[hashman]

@yabwon:

Bart,

You win. Your Roger-inspired simplicity beats mine ;). 

Kind regards

Paul D.

[yabwon]

Paul,

I know I'm echo-ing Roger's opinion but I think if only the dosubl() could get better doc. [giving us more explanation] and work a bit faster it would be really great solution.

All the best

Bart

[Quentin]

DoSubl calling Call Execute.  I like it!

While on the topic of DoSubl, it's always worth mentioning Mr. Langston's seminal DoSubl function-style macro, which is of course easy to extend to add a sort option:

%MACRO ExpandVarList(data=_LAST_, var=_ALL_,sort=0);
  %local orderbyclause ;

  %if &sort=1 %then %let orderbyclause=order by _name_ ;

  %if %upcase(%superq(data)) = _LAST_ %then %let data = &SYSLAST;
  %let rc = %sysfunc(dosubl(%nrstr(
  proc transpose data=&DATA(obs=0) out=ExpandVarList_temp;
    var &VAR;
  run;
  proc sql noprint;
    select _name_ into :temp_varnames separated by ' '
    from ExpandVarList_temp
    &orderbyclause
  ;
  drop table ExpandVarList_temp;
  quit ;
  )));
&temp_varnames
%MEND ExpandVarList;


data have;
input d  c $ B A $;
cards;
1 x 2 y
;
run;

options mprint ;
data want;
  retain %ExpandVarList(data=have,sort=1) ;
  set have;
run;

And agree, of course, that we deserve more documentation for DOSUBL.  Would be lovely if they could speed up the execution, but in fairness, it's doing a LOT of work that CALL EXECUTE doesn't have to do.

[yabwon]

@Quentin ,

hehehe, call execute inside dosubl, I didn't realised I did it until you pointed it out 😄

About "calling something inside something" - my favour (and kind of revelation too) was Mike Rhoads' macro-function-sandwich concept from: https://support.sas.com/resources/papers/proceedings12/004-2012.pdf

I also agree that dosubl() has a lot more work to do "before" comparing to call execute(), but what I would really like SAS to have would be function like: experssion(), which would accept texstring (containing datastep expression) and would try to evaluate it basing on current datastep values, this would give us possibility to evaluate dynamically created expression. 

All the best

Bart