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
This does work. name=TRANWRD(details,'!STREET',SUBSTR(var1,1,4)); I was forgetting the last ).
Thx for all the help!!!!
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;
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;
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
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;
This does work. name=TRANWRD(details,'!STREET',SUBSTR(var1,1,4)); I was forgetting the last ).
Thx for all the help!!!!
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.
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.