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!!!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.