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.
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
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
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.
Your datasets are SAS dataset ? The first character of SAS variable name must be an English letter (A, B, C, . . ., Z) or underscore (_).
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.
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
Thank you, I learn something new.
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
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.