topic Re: Replace characters with a substring from a different variable. in SAS Programming

Replace characters with a substring from a different variable.

a079011 — Wed, 13 Sep 2017 12:10:42 GMT

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

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

PeterClemmensen — Wed, 13 Sep 2017 12:21:54 GMT

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;

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

SAS_inquisitive — Wed, 13 Sep 2017 12:24:18 GMT

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;

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

a079011 — Wed, 13 Sep 2017 12:43:08 GMT

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

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

PeterClemmensen — Wed, 13 Sep 2017 12:53:30 GMT

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;

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

a079011 — Wed, 13 Sep 2017 12:56:02 GMT

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

Thx for all the help!!!!