DATA Step, Macro, Functions and more

Replace characters with a substring from a different variable.

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Replace characters with a substring from a different variable.

 
 I have been trying to use TRANWORD with a subsring, for example
 name=TRANWRD(details,'!STREET',SUBSTR(var1,1,4); 
 Not sure there is a way to do this with TRANWORD or TRANSLATE. Or what is the best approach. Thx for the help in advance!

 

** DATA HAVE **

 VAR1      DETAILS
 MAIN       OHIO.!STREET
 MAIN       OHIO.!STREET
 EAST       TEXAS.!STREET
 RUNN      GEORGIA.!STREET
 HOPP      TEXAS.!STREET
 
 ** DATA WANT **
 OHIO.MAIN
 OHIO.MAIN
 TEXAS.EAST
 GEORGIA.RUNN
 TEXAS.HOPP


Accepted Solutions
Highlighted
Solution
2 weeks ago
Contributor
Posts: 21

Re: Replace characters with a substring from a different variable.

This does work. name=TRANWRD(details,'!STREET',SUBSTR(var1,1,4)); I was forgetting the last ).

 

Thx for all the help!!!!

View solution in original post


All Replies
PROC Star
Posts: 634

Re: Replace characters with a substring from a different variable.

like this?

 

data have;
input VAR1$ DETAILS$20.;
datalines;
MAIN OHIO.!STREET
MAIN OHIO.!STREET
EAST TEXAS.!STREET
RUNN GEORGIA.!STREET
HOPP TEXAS.!STREET
;

data want(keep=name);
	set have;
	name = tranwrd(DETAILS, scan(DETAILS,-1,'.'), VAR1);
run;
Super Contributor
Posts: 256

Re: Replace characters with a substring from a different variable.

data have;
input VAR1 $ DETAILS $ 20. ;
cards;
 MAIN OHIO.!STREET
 MAIN OHIO.!STREET
 EAST TEXAS.!STREET
 RUNN GEORGIA.!STREET
 HOPP TEXAS.!STREET
 ;
run;

data want;
	set have;
        var =catx('.',scan(details,1,'.!'),VAR1);
	keep var;
run;
Contributor
Posts: 21

Re: Replace characters with a substring from a different variable.

Just realized I forgot to add that !STREET won't always be after the first period.

 

** DATA HAVE **

 VAR1      DETAILS
 MAIN       OHIO.!STREET
 MAIN       !STREET.OHIO
 EAST       ARK.TEXAS.!STREET
 RUNN      GEORGIA.!STREET.ATL
 HOPP      TEXAS.HOUTON.!STREET.HOME
 
 ** DATA WANT **
OHIO.MAIN
MAIN.OHIO
ARK.TEXAS.EAST
GEORGIA.RUNN.ATL

TEXAS.HOUTON.HOPP.HOME

PROC Star
Posts: 634

Re: Replace characters with a substring from a different variable.

But the string to be replaced is always exactly "!STREET"? In that case

 

data have;
input VAR1$ DETAILS$50.;
datalines;
MAIN OHIO.!STREET
MAIN !STREET.OHIO
EAST ARK.TEXAS.!STREET
RUNN GEORGIA.!STREET.ATL
HOPP TEXAS.HOUTON.!STREET.HOME
;

data want(keep=name);
	set have;
	name = compress(tranwrd(DETAILS, "!STREET", VAR1));
run;
Highlighted
Solution
2 weeks ago
Contributor
Posts: 21

Re: Replace characters with a substring from a different variable.

This does work. name=TRANWRD(details,'!STREET',SUBSTR(var1,1,4)); I was forgetting the last ).

 

Thx for all the help!!!!

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 129 views
  • 5 likes
  • 3 in conversation