How to remove a "prefix" from relevant variables

[GregG]

I pull from a database that has sum, but not all, variables prefixed with XCBM_

I would like to remove all of these prefixes from the variables name.

Here is what I have tried:

DATA TEST;

    SET SASHELP.BWEIGHT;

RUN;

%MACRO RENAME(LIB=,DSN=);

PROC CONTENTS DATA=&LIB..&DSN;

    TITLE 'BEFORE';

RUN;

PROC SQL NOPRINT;

    SELECT    NVAR INTO :NUM_VARS

    FROM    DICTIONARY.TABLES

    WHERE    LIBNAME="&LIB" AND MEMNAME="&DSN"

    ;

    SELECT    DISTINCT(NAME) INTO :VAR1-:VAR%TRIM(%LEFT(&NUM_VARS))

    FROM    DICTIONARY.COLUMNS

    WHERE    LIBNAME="&LIB" AND MEMNAME="&DSN"

    ;

QUIT;

%PUT &NUM_VARS;

PROC DATASETS LIBRARY = &LIB;

    MODIFY &DSN;

    RENAME

    %DO I = 1 %TO &NUM_VARS.;

        %IF %SUBSTR(&&VAR&I,1,2) = 'm_' %THEN %DO;

            &&VAR&I = %SUBSTR(&&VAR&I,3);

        %END;

    %END;

    ;

RUN;

QUIT;

PROC CONTENTS DATA=&LIB..&DSN.;

    TITLE 'AFTER';

RUN;

%MEND RENAME;

%RENAME(LIB=WORK,DSN=TEST)

[Reeza]

How many variables do you expect? Can you use a single macro variable for the rename statement or would you expect it to be longer than 62K characters?

[Reeza]

Example could be adapted to work with prefixes below:

/********************************************************************

Example : Rename variables based on suffix rather than prefix

********************************************************************/

data sample;

do i=10000 to 12000;

start_date=i;

middle_date=i+3;

end_date=i+5;

date_no_change=start_date;

output;

end;

format start_date end_date middle_date date9.;

run;

proc sql noprint;

select catx("=", name, catt('DT_', tranwrd(upper(name), '_DATE', ' ')))

into :rename_list

separated by " "

from sashelp.vcolumn

where libname='WORK'

and memname='SAMPLE'

and upper(trim(name)) like '%_DATE';

quit;

%put &rename_list;

proc datasets library=work nodetails nolist;

modify sample;

rename &rename_list;

run; quit;

proc print data=sample noobs;

run;