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
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 |
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.