Rodney,

Here is a possible solution. The limitation is that a macro variable cannot be over 200 char since I use %sysfunc.

1) one needs chop off the prefix 'G' by using compress function. 2) one replaces delimiter ' ' with ',' so that one can call the data step function ordinal. 3) a looping will create a new macro variable in order.

The same idea can be done w/o using %sysfunc w/ more loopings if your macro var is over 200 char.

Hope this helps.

546 %let a=G1 G10 G11 G12 G17 G14 G2 G3 G4 G550 G6 G7 G8 G9; 547 %let mvarnum=%sysfunc(TRANSLATE(%sysfunc(compress(&a,G)),',',' '));

548 %let a1=; 549 %let g=G; 550 551 %macro mvarsort; 552 %local i; 553 %let i=1; 554 %let word=%scan(&a,&i); 555 %let a1=; 556 %do %until ( &word=%str( ) ); 557 558 %let a1=&a1 &g%sysfunc(ordinal(&i,&mvarnum)); 559 %let i=%eval(&i+1); 560 %let word=%scan(&a,&i); 561 562 %end; 563 %put &a1; 564 565 %mend; 566 567 %mvarsort G1 G2 G3 G4 G6 G7 G8 G9 G10 G11 G12 G14 G17 G550

RODNEY PRESLEY wrote:

> SAS-L, > > I have a data set with a series of variables > with these names: > > firstvar .... ID NAME CODE MUGVAR > .............. > C1G1_CI C1G1_FOM C1G1_SFL C1G1_SOE > C1G2_CI C1G2_FOM C1G2_SFL C1G2_SOE > C1G3_CI C1G3_FOM C1G3_SFL C1G3_SOE > C2G4_CI C2G4_FOM C2G4_SFL C2G4_SOE > C2G5_CI C2G5_FOM C2G5_SFL > C2G5_SOE C2G6_CI C2G6_FOM C2G6_SFL > C2G6_SOE C3G7_CI C3G7_FOM C3G7_SFL > C3G7_SOE C3G8_CI C3G8_FOM > C3G8_SFL C3G8_SOE C3G9_CI C3G9_FOM > C3G9_SFL C3G9_SOE C4G10_CI C4G10_FM > C4G10SFL C4G10_SE > C4G11_CI C4G11_FM C4G11SFL C4G11_SE > C4G12_CI C4G12_FM C4G12SFL C4G12_SE > C4G13_CI C4G13_FM > C4G13SFL C4G13_SE C5G14_CI C5G14_FM > C5G14SFL C5G14_SE ....lastvar > > The following select clause in a PROC SQl : > > select distinct > compress(substr(name,(index(name,'G')),3),'_') > into: name_g separated by ' ' > from dictionary.columns > where libname='ESRD' > and memname="%upcase(&dsnold)" > and type='num' > and (substr(name,1,1)='C' > and (substr(name,3,1)='G')) ; > > generates this resulting macro variable: > > SYMBOLGEN: Macro variable NAME_G > resolves to G1 G10 G11 G12 G13 G14 G2 G3 > G4 G5 G6 G7 G8 G9 > > What I want for the macro variable NAME_G > is: > > G1 G2 G3 G4 G5 G6 G7 G8 G9 G10 G11 G12 > G13 G14 > > Can I change the way "distinct" causes the > observations to be sorted? Or is there a prefix > or suffix that could be added to each element > of NAME_G so that "distinct" would cause > sorting in the desired way? Any other ideas > appreciated. > > This is only my second application of > dictionary tables and stuffing macro variables > using PROC SQL. It is so useful and cool I > would hate to have to give up on this and do it > another way. > > Thank you in advance for your help. > > Rodney > > Rodney J. Presley, Ph.D. > PRO-West > 10700 Meridian Av. N., suite 100 > Seattle, WA 98133 > > 206-364-9700 voice > 206-440-2644 fax > > wapro.rpresley@sdps.org