BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gzr2mz39
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

7 REPLIES 7
Haikuo
Onyx | Level 15

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

gzr2mz39
Quartz | Level 8

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.

slchen
Lapis Lazuli | Level 10

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

gzr2mz39
Quartz | Level 8

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.

Haikuo
Onyx | Level 15

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

slchen
Lapis Lazuli | Level 10

Thank you, I learn something new.

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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