Date:         Wed, 10 Nov 2010 22:10:37 +0000
Reply-To:     "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
Subject:      Re: Renaming Variables Using a Macro
Comments: To: Eduardo Galvan <egalvan@ISR.UMICH.EDU>
In-Reply-To:  <201011102012.oAAJhGLe022774@willow.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Eduardo:

While Ron's advice to declare macrovar VARLIST to be LOCAL is good practice in this case, it doesn't answer your original question.

The only difference in your two calls is the second argument, the dataset name.  Do those datasets actually have different variables WHOSE NAMES BEGIN WITH V?  If not, then that would explain why VARLIST appears to be a constant, whether global or local.  But if there is some other indication that VARLIST should be different, let us know.

An uninvited comment:  I presume you are making the work dataset first to
 (1) prevent the "dropped" variables from being discoverable in the PROC SQL, and ..
 (2) add variable YEAR with a constant value.

If so, instead of creating the dataset and then doing the rename, how about renaming as you create it, as in:

  proc sql nopRint;
    ** Create view of 1 obs, just to get the reduced varlist **;
    create view v1 as select * from &libdir..&file (obs=1 drop=&vardrop);

    ** Now get the parameters for the RENAME statement;
    select trim(name)||'=CX'||substr(name,2)
      into :varlist separated by ' '
      from DICTIONARY.COLUMNS
      WHERE LIBNAME EQ "WORK" and MEMNAME EQ "V1" and
            upcase(name) like '&repvar.%';
  quit;


  data work.&file (drop=&vardrop);
    set &libdir..&file (drop=&vardrop);
    Year=&year;
  run;

Adding the CREATE VIEW statement eliminates the need for PROC DATASETS in your example.



Regards,
Mark

> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Eduardo Galvan
> Sent: Wednesday, November 10, 2010 3:13 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Renaming Variables Using a Macro
>
> Hi,
>
> I am tying to change the naming of variables in 28 files. The variable
> names in each of these files start with a "V" prefix that I want to
> change
> to CX.
>
> The code I've pasted below works on the first macro pass, but doesn't
> work
> for the second and subsequent passes because the values stored in the
> varlist remain the same through these passes.
>
> Any suggestions on fixing this would be appreciated.
>
>
> %macro cumfile (libdir, file, vardrop, year, repvar);
>
> data work.&file (drop=&vardrop);
> set &libdir..&file;
> Year=&year;
> run;
>
>
> proc sql
> noprint;
>
>   select trim(name)||'=CX'||substr
> (name,2)
>
>   into :varlist separated
> by ' '
>
>   from
> DICTIONARY.COLUMNS
>
>   WHERE LIBNAME EQ "WORK" and MEMNAME
> EQ "&file"
>
>   and upcase(name)
> like '&repvar.%';
>
> quit;
>
> %put &varlist;
>
> /* Use PROC DATASETS to do the rename. Again, verify the libref and
> */
> /* member name match your data set.
> */
>
>
>
> proc datasets library=work
> nolist;
>
>   modify
> &file;
>
>   rename
> &varlist;
>
> quit;
>
>
> %mend cumfile;
>
>
>
>
>
> %cumfile (RAW08, X08A_R, VSUBHH KSUBHH VPN_SP, 2008, V)
> %cumfile (RAW08, X08B_R, VSUBHH KSUBHH VPN_SP, 2008, V)
> etc.