Hi,

I have this pieces of code I used to change the order of variables on big files (300 variables and more). The user wanted to have the variables in a determined order. I made a macro of it. You will have to fill a field called newnum to give the new position of your variable. The macro then generate a data step with the correct attrib statements to create an empty model of your new file and append the file on it.

It worked well for me . The reason why I didn't used SQL is that sql (under SAS) has troubles giving you the same length for numeric fields ... It always give you 8 bytes numeric even if you specifiy 3 bytes (Old bug ...).

See code above and have fun!

Bernard Tremblay

\\\|/// \\ - - // ( @ @ ) +------oOOo-(_)-oOOo----------+---------------------------------+ | Bernard Tremblay | | | La Capitale | Tel: (418) 646-2401 | | | Fax: (418) 646-5960 | | | Int: bernard@capitale.qc.ca | +-----------------------------+---------------------------------+ | Imaginasys enr | Res: (418) 878-4447 | | | Int: bertrem@quebectel.com | +---------------Oooo----------+---------------------------------+ oooO ( ) ( ) ) / \ ( (_/ \_) *============================== SAS CODE follows =====================;

%macro varsort(lib1, mem1, lib2,mem2); %*-----------------------------------------; %* lib1 = library of input dsn to "varsort"; %* mem1 = member name of dsn to "varsort"; %* lib2 = library of output dsn ; %* mem2 = member name of output dsn ; %*-----------------------------------------; %*; *; *============================================================================*; * MACRO VARSORT *; * Order the variable of a SAS dataset according to one*s order... *; * Bernard Tremblay, 950401 *; *============================================================================*; *; %let lib1 = %upcase(&lib1); /* SQL uses upcase... */ %let mem1 = %upcase(&mem1); *; proc sql; *; **** Obtain the dsn variables ; *; create table data1 as select varnum*10 as newnum, * from DICTIONARY.COLUMNS where LIBNAME = "&lib1" & MEMTYPE = "DATA" & MEMNAME = "&mem1" order by name ; quit; *; **** You have to reorder the variables by changing the variable newnum ***; *; proc fsview data=data1 edit;run; *; proc sort data=data1; by newnum;run; *; **** We will create a new empty DSN with ordered vars ***; **** Let*s call it the "model DSN "... ***; *; data _null_; set data1 end=eof; if _n_ = 1 then call execute("data &lib2..&mem2 ;"); call execute("attrib "||name); if length > 0 then if type = 'char' then call execute('length=$'||put(length,3.)); else call execute('length='||put(length,3.)); if format ^ = "" then call execute("format="||format); if informat ^ = "" then call execute("informat="||informat); if label ^= "" then call execute('label="'||label||'"'); call execute(";"); if type = "num" then call execute(name||"= . ;"); else call execute(name||"= '';"); if eof then call execute("delete; stop; run;"); run; *; *; **** Now we append the old dsn to the model DSN we created *; *; proc append base=&lib2..&mem2 data=&lib1..&mem2 force ;run; *; **** Finish ; *; %mend varsort;

*================ END of SAS code ================================;

>>>From owner-sas-l@UGA.CC.UGA.EDU Mon Jan 20 14:12 EST 1997 >>>Mime-Version: 1.0 >>>Date: Mon, 20 Jan 1997 12:58:53 -0600 >>>From: Thomas Kalfas <kalfast@PPRD.ABBOTT.COM> >>>Subject: Re: Changing the order/Use ARRAY not RETAIN >>>Comments: To: John Whittington <johnw@mag-net.co.uk>, >>> Robert Hamer <hamer@RCI.RUTGERS.EDU> >>>To: Multiple recipients of list SAS-L <SAS-L@UGA.CC.UGA.EDU> >>> >>>On Jan 19, 4:03am, John Whittington filibustered: >>> >>>> <snip>...(and the workers of the world cheered)... >>> >>>> Thomas Kalfas offered a 'macro-ised' version of RETAIN to be used for >>>> re-ordering. This is also 'foolproof', when it works, because it will >>>> actually refuse to work, and generate an 'error message', if one attempts to >>>> re-order a variable which was *not* present in the specified input dataset; >>> >>>I'm glad you like it ;-). There is also dataset and parameter checking. >>> >>>> this approach is, however, obviously of no use if one actually *wants* to >>>> include a variable created within the DATA step in the re-ordering! If one >>> >>>Now you got it! Hence the appropriate macro name, %REORDER. Remember, the >>>purpose of the macro was to protect against potential misuse of the RETAIN >>>statement (yes, I know you're maintaining that RETAIN is toothless). A side >>>benefit is that, for the lost soul who had originally posted that he had needed >>>to do this reordering for a large number of datasets, I would expect it to be >>>quicker and easier to fill in the macro parameters rather than the 3 or 4 lines >>>of datastep code (4 if you include the RUN;...and I always do); and, as with >>>any lengthy, repetitive task (such as fending off stubborn cynics) there is a >>>greater need for error checking / entry validation. >>> >>>> one would therefore say that the macro works when it is not needed (i.e. when >>>> there could be no problem with a non-macro RETAIN), but not when there is a >>>> potential problem of RETAIN (i.e. when one wishes to re-order variables not >>>> in the input dataset). >>> >>>Uhg! Now you're the one changing the rules of the game! Haven't we been >>>talking about simply reordering existing variables in existing datasets? In >>>any case, I had long since jumped off the rocky RETAIN boat and onto the good >>>ship SQL, i.e. I changed the reordering method from a RETAIN to a PROC SQL >>>(w/order stmt) step. >>> >>>John, you're really "holding on" to this RETAIN thing. I'm afraid that you >>>might become synonymous with it and I will have to search for other methods of >>>retaining variables... :-). >>> >>>> however, serve as a useful 'mind jogger' for programmers, because if they >>>> tried to use it and failed, they would be alerted to the need to consider >>>> explicit resetting to missing of some variables! >>> >>>Now there's a market! Keep an eye open for upcoming macros %TM4LUNCH, >>>%PAYDBILZ, and %WYFSBDAY. >>> >>>> ... my two pence worth, anyway, >>> >>>Who's collecting these pence, and how do I get this job? >>> >>>Tom >>> >>>(P.S. - How's your arm feeling? 8^} ) >>> >>>-- >>>Thomas Kalfas >>>System Developer >>>Abbott Laboratories >>> >>>E-mail: kalfast@pprd.abbott.com >>> >>>Phone: (847) 938-8101 >>>Fax: (847) 935-1320