DATA Step, Macro, Functions and more

macro for renaming many variables?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 196
Accepted Solution

macro for renaming many variables?

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.


Accepted Solutions
Solution
‎06-02-2014 08:16 PM
Respected Advisor
Posts: 3,124

Re: macro for renaming many variables?

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: macro for renaming many variables?

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

Regular Contributor
Posts: 196

Re: macro for renaming many variables?

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.

Super Contributor
Posts: 275

Re: macro for renaming many variables?

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

Regular Contributor
Posts: 196

Re: macro for renaming many variables?

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.

Solution
‎06-02-2014 08:16 PM
Respected Advisor
Posts: 3,124

Re: macro for renaming many variables?

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

Super Contributor
Posts: 275

Re: macro for renaming many variables?

Thank you, I learn something new.

Respected Advisor
Posts: 3,124

Re: macro for renaming many variables?

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 299 views
  • 3 likes
  • 3 in conversation