BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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

2 REPLIES 2
FriedEgg
SAS Employee

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1134 views
  • 0 likes
  • 3 in conversation