dynamic columns are in sequence order

[sunilreddy]

qucIn report, I have a static and dynamic column lables in below, column1 to column5 are Static and remaining all are dynamic columns

These dynamic columns are changing depending on selection. i.e. dynamic prefix date columns are varying

Source Column_names: Cycle, pL1, PL2, PL3,Supp name, 2012-05 Global ,2012-05 Region, 2012-05 Customer ,2012-03 Global,2012-03 Region,2012-03 Customer,
                                       2012-04 Global ,2012-04 Region,2012-04 Customer.

Target Column_names: Cycle, pL1, PL2, PL3,Supp name, 2012-03 Global,2012-03 Region,2012-03 Customer, 2012-04 Global ,2012-04 Region,2012-04 Customer, 2012-05 Global,2012-05  

                                      Region, 2012-05 Customer.                             

        .

Can any one help me to sequence these dynamic columns in order. All dynamic columns Prefix date would in sequence order and suffix characters sequence would be Global, Region, Customer

[FriedEgg]

proc sql;

create table foo(cycle               num

                 ,pl1                 num

                 ,pl2                 num

                 ,pl3                 num

                 ,sm                  num

                 ,'2012-05 Global'n   num

                 ,'2012-05 Customer'n num

                 ,'2012-03 Global'n   num

                 ,'2012-03 Region'n   num

                 ,'2012-03 Customer'n num

                 ,'2012-04 Global'n   num

                 ,'2012-04 Region'n   num

                 ,'2012-04 Customer'n num);

select name

   from dictionary.columns

  where libname='WORK' and memname='FOO'

  order by case(scan(name,2,' '))

            when 'Global' then 2

            when 'Region' then 3

            when 'Customer' then 4

                  else 1

           end,

           case

            when countw(name)=1 then varnum

            else input(scan(name,1),anydtdte.)

           end;

quit;

cycle

pl1

pl2

pl3

sm

2012-05 Global

2012-04 Global

2012-03 Global

2012-04 Region

2012-03 Region

2012-05 Customer

2012-04 Customer

2012-03 Customer

[Ksharp]

I know FriedEgg have already given you a good solution.

options validvarname=any;
proc format;
invalue $ infmt
       'Global'=1
       'Region'=2
       'Customer'=3;
run;
proc sql;
create table foo(cycle               num
                 ,pl1                 num
                 ,pl2                 num
                 ,pl3                 num
                 ,sm                  num
                 ,'2012-05 Global'n   num
                 ,'2012-05 Region'n   num
                 ,'2012-05 Customer'n num
                 ,'2012-03 Global'n   num
                 ,'2012-03 Region'n   num
                 ,'2012-03 Customer'n num
                 ,'2012-04 Global'n   num
                 ,'2012-04 Region'n   num
                 ,'2012-04 Customer'n num);
insert  into foo  values(1,1,1,1,1,1,1,1,1,1,1,11,1,1);

select cats('"',name,'"n') into : list separated by ' '
   from dictionary.columns
    where libname='WORK' and memname='FOO' and  name not in ('cycle' 'pl1' 'pl2' 'pl3' 'sm') 
     order by input(compress(name,,'kd'),best8.) desc, input(scan(name,-1),$infmt.);
quit;

proc print data=foo noobs;
var cycle pl1 pl2 pl3 sm &list;
run;

Ksharp