Rrearrange order of column by columns name

Question

I have dataset like follows;

data dataset;
input name $ mob5 mob1 mob3 x;
datalines;
a 1 3 5 7
b 2 4 6 8
c 3 5 7 9 
d 5 7 9 2
;

run;

I would like to select the fields name and those with mob (UNKNOW columns name and number of columns contain mob). i dunno how to use retain i do not know how many of columns with columns name contains mob.

proc sql;
    create table table1 as
    select *
    from dataset(keep=name mob:)
quit;

My desired output will be

name mob1 mob3 mob5
a 3 5 1
b 4 6 2
c 5 7 3
d 7 9 5

Answer 1

You can use the dictionary tables for this (assuming your source dataset is called 'dataset' and resides in the work library, make changes to the WHERE clause if not, but make sure you use upper-case for the values):

PROC SQL;
  SELECT name INTO: mob_cols SEPARATED BY ','
  FROM dictionary.columns
  WHERE libname = 'WORK' and memname = 'DATASET' 
  AND upcase(name) LIKE 'MOB%'
  ORDER BY name;
QUIT;

This code loads all of the 'mob' columns into a macro variable, ordered by name and separated by comma.

Then you can use this macro variable in the SELECT clause of your PROC SQL:

PROC SQL;
  CREATE TABLE table1 AS
  SELECT name,
  &mob_cols.
  FROM dataset;
QUIT;