topic Re: dynamic columns are in sequence order in SAS Programming

dynamic columns are in sequence order

sunilreddy — Mon, 16 Apr 2012 18:23:36 GMT

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

Re: dynamic columns are in sequence order

FriedEgg — Mon, 16 Apr 2012 18:55:23 GMT

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

2012-05 Global

2012-04 Global

2012-03 Global

2012-04 Region

2012-03 Region

2012-05 Customer

2012-04 Customer

2012-03 Customer

Re: dynamic columns are in sequence order

Ksharp — Tue, 17 Apr 2012 06:30:02 GMT

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