Date:         Thu, 21 Oct 2004 12:44:35 -0400
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: Column name

Here's one way which should avoid the scale problems.

Use a DATA step instead of SQL and a %INCLUDEd file instead of a
macrovariable. For example:

   data test;
   retain alpha beta gamma delta omega 1;
   run;

   filename rename catalog 'work.rename.data.output';

   data _null_;
   set sashelp.vcolumn end=done;
   where libname = "WORK" and memname = "TEST" and varnum>3;
   file rename;
   renamer = compress(name||'='||'x'|| input(put(varnum-3,8.),$8.) );
   put renamer;
   run;

   proc datasets library=work;
   modify test;
   rename %include rename ;
   ;
   run;

Notice the semicolon standing on its own line. It closes the RENAME
statement; the previous semicolon terminates the %INCLUDE.

The log shows:

   NOTE: Renaming variable delta to x1.
   NOTE: Renaming variable omega to x2.

By the way, the formula in the DATA step is different from the one I used
yesterday in SQL; $8. rather than $2. as the informat. I don't understand
why the $2. worked. I am going to start a separate thread about that.

On Wed, 20 Oct 2004 14:28:15 -0600, Michael Murff <mjm33@MSM1.BYU.EDU>
wrote:

>I hesitate to mention a potential constraint on Toby's routine, since I
>don't presently have a solution to my observation; and admittedly, it's
>easier to critique a program than write one from scratch. The macro var
>strategy at issue will work for all but very wide datasets and/or those
>datasets with very long variable names. A single macro variable can only
>contain so many characters (or bytes), though I'm not sure what the
>exact maximum is. And from the looks of my log, there the macro var
>"rename" is truncated, but something else must be going on because I get
>the following errors when I try to resolve it in the rename step.
>
><snip from my log>
>ERROR: Overflow has occurred; evaluation is terminated.
>ERROR: Out of memory.
><end>
>
>Nevertheless, I have needed a routine of this ilk for some time and it
>will make a welcome addition to my rather small, but growing tool kit.
>
>Thanks Toby (and Claudia for raising the question),
>
>M.M.
>
>*******************************;
>%macro mockdata;
>
>%let NCOLS = 10000;
>%let NOBS = 20;
>%let SEED = 20041008;
>
>/* mock up market dataset */
>data test;
>  do row = 1 to &NOBS;
>    %local i;
>    %do i = 1 %to &NCOLS;
>      co&i._pr = round (10 * ranuni(&seed), 0.0001);
>    %end;
>    output;
>  end;
>  drop row;
>run;
>
>%mend;
>
>%mockdata;
>
>
>proc sql noprint;
>select compress(name||'='||'x'||input(put(varnum,8.),$4.))
>       into : rename separated by ' '
> from sashelp.vcolumn
>  where libname = "WORK" and memname = "TEST";
>quit;
>
>%put _user_;
>
>
>data test2;
>set test (rename = (&rename));
>run;
>*******************************;
>
>>>> "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US> 10/20/04 7:38 AM >>>
>Claudia,
>
>The easiest way I can figure is through a SQL into statement hitting
>your meta data:
>
>
>Such as this example:
>
>data test;
> alpha = 1;
> beta  = 1;
> omega = 1;
>run;
>
>proc sql noprint;
> select compress(name||'='||'x'||input(put(varnum,8.),$2.))
>        into : rename separated by ' '
>  from sashelp.vcolumn
>   where libname = "WORK" and memname = "TEST";
>quit;
>
>
>data test2;
> set test (rename = (&rename));
>run;
>
>proc contents
> data = test2;
>run;
>
>Hth
>Toby Dunn
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Claudia
>Sent: Tuesday, October 19, 2004 9:33 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Column name
>
>Hi,
>
>I imported an excel file into SAS.  Now I would like to change the
>column (or array) names so that it's easier for me to compute my
>models afterwards.
>
>I know that I could either change them directly in excel or again
>directly in SAS using for instance var1=new_varname, but since I have
>so many columns (more than 1,000), I don't want to do this by hand.
>
>Is there a way (I guess using a loop) to rename my columns x1, x2, x3,
>etc...  without going one at a time?
>
>Starting at the 4th column, I would like to name my columns x1, x2,
>...xn
>
>Sorry for the newbie question,
>
>Claudia