BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
silvergrenade
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

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;
gamotte
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12
You're right, i misread the question.
silvergrenade
Obsidian | Level 7

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.

 

 

Kurt_Bremser
Super User

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.

silvergrenade
Obsidian | Level 7

Thankks for taking the time to help me out. Its working perfectly. Your suggestions too, helped. Thanks.

DanielSantos
Barite | Level 11

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 971 views
  • 2 likes
  • 4 in conversation