Variable sorting tables by prefix / start letter

Question

This is for the SAS table, so SQL commands will also work.

I have a table with 300 variables; they have 5 different prefixes that I would like to sort. I want them in a specific order (prefix mtr prefix before date), but in alphabetical order would be acceptable.

I thought SQL would have something like:

Select mtr*, date* from Table

or

Select mtr%, date% from Table

Answer 1

As gbn says, you need to get the column names and dynamically build some sql (or data step code).

, SAS, , , , :

proc sql noprint;
  select name into :orderedVarNames separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='YOUR_TABLE_NAME'
  order by name
  ;
quit;

(, libname .) , :

proc sql;
  select &orderedVarNames
  from YOUR_TABLE_NAME
  ;
quit;

"& orderedVarNames" . , , : %put &orderedVarNames;

, , , , . , separated by ' '.

, , , , sashelp.vcolumn. - :

proc sql noprint;
  select name into :orderedMTRvars separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='MYTABLE' and substr(name,1,3)='MTR'
  order by name
  ;
  select name into :orderedDATEvars separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='MYTABLE' and substr(name,1,4)='DATE'
  order by name
  ;
  quit;

  proc sql;
    select &orderedMTRVars, &orderedDATEVars
    from MYTABLE
    ;
  quit;