topic Re: macro for renaming many variables? in SAS Programming

macro for renaming many variables?

gzr2mz39 — Mon, 02 Jun 2014 22:25:44 GMT

I have multiple datasets with many variables that begin with '/bic/ and end with 'n.

Does anyone know of a macro that will remove these characters from the beginning and end of variables with these specific characters?

For example, '/bic/ZHRIMMNA'n should be ZHRIMMNA while variables without these characters would be left alone.

Thank you.

Re: macro for renaming many variables?

Haikuo — Mon, 02 Jun 2014 23:26:27 GMT

Something like the following may fit your bill:

data have;

input '/bic/var1'n '/bic/var2'n;

cards;

1 2

;

proc sql;

select cats("'",name,"'N","=", scan(name,2,"/")) into :ren separated by ' '

from dictionary.columns

where LIBNAME='WORK' AND MEMNAME='HAVE';

QUIT;

DATA WANT;

SET HAVE;

RENAME &ren;

RUN;

Regards,

Haikuo

Re: macro for renaming many variables?

slchen — Tue, 03 Jun 2014 00:01:08 GMT

Your datasets are SAS dataset ? The first character of SAS variable name must be an English letter (A, B, C, . . ., Z) or underscore (_).

Re: macro for renaming many variables?

gzr2mz39 — Tue, 03 Jun 2014 00:04:30 GMT

My data is coming from SAP. It comes into SAS fine. However, I need to rename the variables after they come in as you stated above.

Re: macro for renaming many variables?

gzr2mz39 — Tue, 03 Jun 2014 00:06:34 GMT

Haikuo, does this SQL code ignore variables that don't begin with '/bic/ and end with 'n?

I haven't been able to test it yet.

Re: macro for renaming many variables?

Haikuo — Tue, 03 Jun 2014 00:16:29 GMT

OP's SAS table was imported from third party, as a result, the variable names is in literal. In EG, literals can be used by default, while if you are on Base, you may need to set the system option as: options validvarname=any;

Regards,

Haikuo

Re: macro for renaming many variables?

slchen — Tue, 03 Jun 2014 00:28:17 GMT

Thank you, I learn something new.

Re: macro for renaming many variables?

Haikuo — Tue, 03 Jun 2014 00:52:26 GMT

You granted me the "correct answer" before I deliver a correct one. The previous one only works if all of your variables are in the format of /bic/, you would need more fire power than scan() function if you want to ignore those not in literals.

data have;

input '/bic/var1'n '/bic/var2'n var3;

cards;

1 2 3

;

proc sql;

select cats("'",name,"'N","=", prxchange('s/\/bic\///io',-1,name)) into :ren separated by ' '

from dictionary.columns

where LIBNAME='WORK' AND MEMNAME='HAVE';

QUIT;

DATA WANT;

SET HAVE;

RENAME &ren;

RUN;

Regards,

Haikuo