Date:         Mon, 26 Jan 2004 12:12:46 -0500
Reply-To:     "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Subject:      Re: How to rename variables.(update)
Content-Type: text/plain; charset="iso-8859-1"

Jean-Paul:

In the event that this rename exercise is not a one time event, the solution
below is based on the yyyymm values in the variables.

/*  Create test data  */

data a;
  array rev{*} Revenue_200304 Revenue_200305 Revenue_200306 Revenue_200307
Revenue_200308;
  array us{*}  Usage_200304 Usage_200305 Usage_200306 Usage_200307
Usage_200308;
  array reg{*} Region_200304 Region_200305 Region_200306 Region_200307
Region_200308;
  array val{*} Value_200304 Value_200305 Value_200306 Value_200307
Value_200308 ;
run;

proc sql ;

  /*  Derive maximum month value, i.e. the one that'll turn into M0 */

  select input(max(scan(name,2,'_')),yymmn6.)
    into :max
    from sashelp.vcolumn
   where upcase(libname) = 'WORK'
     and upcase(memname) = 'A'
     and upcase(name)  eqt 'REVENUE'
   ;

  %put &max;

  /*  Based on max month value, derive what's essentially a month counter
off the max  */

  select compress(name || '=' ||
                  scan(name,1,'_') || '_M' ||

put(intck('month',input(scan(name,2,'_'),yymmn6.),&max),best.-l))
    into :rename separated by ' '
   from sashelp.vcolumn
  where upcase(libname)  = 'WORK'
     and upcase(memname) = 'A'
     and ( upcase(name)  eqt 'REVENUE' or
           upcase(name)  eqt 'USAGE'   or
           upcase(name)  eqt 'REGION'  or
           upcase(name)  eqt 'VALUE')
  ;

quit;

/*  Dump rename clause  */

%put &rename;

/*  Use DATASETS to rename and display contents  */

proc datasets lib=work ;
  modify a;
    rename &rename;
  run;
  contents data=a;
  run;
quit;

-----Original Message-----
From: Isson, Jean-Paul [mailto:Jean-Paul.Isson@MICROCELL.CA]
Sent: January 26, 2004 11:28 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to rename variables.(update)


Hi Quentin,
Thanks  a lot  for the help;
But I didn`t really send the exact variables names because I wanted to
simplify
Explanation of my question.
In fact my 150 variables have different names but end up with the same year
month definition.
        Revenue_200304 Revenue_200305 Revenue_200306 Revenue_200307
Revenue_200308
        Usage_200304 Usage_200305 Usage_200306 Usage_200307 Usage_200308
        Region_200304 Region_200305 Region_200306 Region_200307
Region_200308
      ....
      Value_200304 Value_200305 Value_200306 Value_200307 Value_200308  .
And I would like to  rename those variables  in order to have :

        Revenue_M4 Revenue_M3 Revenue_M2 Revenue_M1 Revenue_M0
        Usage_M4 Usage_M3 Usage_M2 Usage_M1 Usage_M0
        Region_M4 Region_M3 Region_M2 Region_M1 Region_M0
      ....
      Value_M4 Value_M3 Value_M2 Value_M1 Value_M0  .

I don't want to rename each variable one by one (going trough 150
variables
>using rename=(Revenue_200304  =Revenue_M4 .. and so forth..).
>
>All ideas or hint are very welcome.
Thanks a lot;
Jean-Paul




-----Message d'origine-----
De : Quentin McMullen [mailto:quentin_mcmullen@BROWN.EDU]
Envoyé : January 25, 2004 8:26 PM
À : SAS-L@LISTSERV.UGA.EDU
Objet : Re: How to rename variables.ive


On Sat, 24 Jan 2004 17:30:16 -0500, Isson, Jean-Paul <Jean-
Paul.Isson@MICROCELL.CA> wrote:

>Hello everybody,
>I Have a dataset with 150 variables let say V1 TO V150 .
>Each variable end up with the year month definition.
>( V1_200304 V1_200305  V1_200306 V1_200307 V1_200308 ,
>  V2_200304 V2_200305  V2_200306 V2_200307 V2_200308 ,
>   ......
>  V150_200304 V150_200305  V150_200306 V150_200307 V150_200308 )
>
>I would like to  rename those variables  in order to have
>
>( V1_M4 V1_M3  V1_M2 V1_M1 V1_M0 ,
>  V2_M4 V2_M3  V2_M2 V2_M1 V2_M0  ,
>   ......
>  V150_M4 V150_M3  V150_M2 V150_M1 V150_M0 ).
> I don't want to rename each variable one by one (going trough 150
variables
>using rename=(v1_200304 =V1_M4.. and so forth..).
>
>All ideas or hint are very welcome.

Hi Paul,

one approach may be a macro like:

%macro rename(numvar=);
  %local v m;
  %do v=1 %to &numvar;
    %do m=4 %to 8 ;
      v&v._2003%sysfunc(putn(&m,z2))=v&v._M%eval(8-&m)
    %end;
  %end;
%mend rename;

107  %put %rename(numvar=2);
v1_200304=v1_M4       v1_200305=v1_M3       v1_200306=v1_M2
v1_200307=v1_M1
v1_200308=v1_M0       v2_200304=v2_M4       v2_200305=v2_M3
v2_200306=v2_M2
v2_200307=v2_M1       v2_200308=v2_M0

So you would call it as:

data new;
  set old (rename=(%rename(numvar=150)));
run;

That said, I'd encourage you to think about reshaping your data to be
something like:

Year Month Value
2003 04    X
2003 05    Y
etc.

Dropping from 750 variables down to 3 (or even two if you combined year and
month into one variable) will likely make solutions easier to code and
probably will run faster too.

Hope that helps,
--Quentin