topic How to remove a "prefix" from relevant variables in SAS Programming

How to remove a "prefix" from relevant variables

GregG — Fri, 15 May 2015 20:23:20 GMT

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;

;

RUN;

QUIT;

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

TITLE 'AFTER';

RUN;

%MEND RENAME;

%RENAME(LIB=WORK,DSN=TEST)

Re: How to remove a "prefix" from relevant variables

Reeza — Fri, 15 May 2015 20:35:32 GMT

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?

Re: How to remove a "prefix" from relevant variables

Reeza — Fri, 15 May 2015 20:43:19 GMT

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;