Date: Mon, 20 Jan 1997 16:13:04 +0500
Reply-To: Bernard Tremblay <bernard@CAPITALE.QC.CA>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Bernard Tremblay <bernard@CAPITALE.QC.CA>
Subject: Re: Changing the order/Use ARRAY not RETAIN
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
| 
