BookmarkSubscribeRSS Feed
GregG
Quartz | Level 8

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)

2 REPLIES 2
Reeza
Super User

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 5182 views
  • 0 likes
  • 2 in conversation