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)
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?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.