Replace characters with a substring from a different variable.

[a079011]

 
 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

[PeterClemmensen]

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;

[SAS_inquisitive]

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;

[a079011]

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

[PeterClemmensen]

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;

[a079011 · Accepted solution]

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

Thx for all the help!!!!