Hi,
I have a data set which consists of say 10 obs. Every observation has 2 variables.(V1, V2)
Also, in another data set i have a list of strings.(V3)
V1 consists of a string present in V3. V2 is the value I want to replace it with.
So, V3 is a long string and I want to put tranwrd(V3, V1, V2) on every value of V3 with all the ten obs of V1 and V2
How do I do this in SAS?
Your help would be greatly appreaciated.
I'd use call execute:
data _null_;
set trantable end=done; /* this is the one with the translation values */
if _n_ = 1 then call execute('data want; set have;');
/* This starts the data step */
call execute('v3 = tranwrd(v3,"' !! trim(v1) !! '","' !! trim(v2) !! '");');
/* This executes the tranwrd function for every v1/v2 pair */
if done then call execute('run;');
/* ends the created data step */
run;
I'd use call execute:
data _null_;
set trantable end=done; /* this is the one with the translation values */
if _n_ = 1 then call execute('data want; set have;');
/* This starts the data step */
call execute('v3 = tranwrd(v3,"' !! trim(v1) !! '","' !! trim(v2) !! '");');
/* This executes the tranwrd function for every v1/v2 pair */
if done then call execute('run;');
/* ends the created data step */
run;
Hello,
data have;
infile datalines dlm=",";
format v1 v2 $20.;
input v1 $ v2 $;
datalines;
foo,baz,
hello,goodbye,
Donald,Mickey
;
run;
data strings;
infile datalines dlm=",";
format v3 $20.;
input v3;
datalines;
foobar,
hello world,
Donald president !
;
run;
data want;
merge have strings;
format new $20.;
new=tranwrd(v3,strip(v1),strip(v2));
run;
@gamotte: I don't think that will work for the OP, as he wants to apply all value pairs to all observations in the second data set.
Hi,
Thanks for your swift replies. Really appreciated. I'm not getting any output though.
proc import datafile="xxxxx.csv"
out=out.tkt_rte_tx_cleaning dbms=csv replace;
getnames=yes;
run;
The above file contains my V1 and V2 called Keyword and Replacement.
TKT_RTE_TX_4 contains the V3.
data _null_;
set out.tkt_rte_tx_cleaning end=done; /* this is the one with the translation values */
if _n_ = 1 then call execute('data out.Multicity_HKG_Route_call_V1;set out2.GCG_HKG_OD_2;');
/* This starts the data step */
call execute('TKT_RTE_TX_4 = tranwrd(TKT_RTE_TX_4,"' !! trim(Replacement) !! '","' !! trim(Keyword) !! '");');
/* This executes the tranwrd function for every v1/v2 pair */
if done then call execute('run;');
/* ends the created data step */
run;
I exported out.Multicity_HKG_Route_call_V1. Theres no change in my V3. What am I doing wrong?
Thanks in advance.
a) do you get messages in the log from the data step created with call execute?
b) do a manual test of the data _null_ step, creating the data step by hand. See what the two trim() functions give you. Look if you have problems with cases (uppercase characters in text, but not in v2, and vice versa).
c) if that doesn't help, post example data (use a data step for convenience) of both datasets. Just a few observations that illustrate the issue.
Thankks for taking the time to help me out. Its working perfectly. Your suggestions too, helped. Thanks.
Hi.
You could also do this with a macro.
%macro doit(HAVE1,HAVE2,WANT);
proc sql noprint;
select V1, V2 into :VONE1 -, :VTWO1 - from &HAVE1;
select count(*) into :VN from &HAVE1;
quit;
data &WANT;
set &HAVE2;
%do I=1 %to &VN;
V3=tranwrd(V3,"&&VONE&I","&&VTWO&I");
%end;
run;
%mend doit;
%doit(have1,have2,want);
This will read the values from have1 into macro variables VONEn, VTWOn. Then do the tranwrd over have2.
Hope it helps.
Daniel Santos @ www.cgd.pt
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.