DATA Step, Macro, Functions and more

Using a dataset values in a function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Using a dataset values in a function

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.

 


Accepted Solutions
Solution
‎01-17-2017 07:00 AM
Super User
Posts: 7,762

Re: Using a dataset values in a function

Posted in reply to silvergrenade

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-17-2017 07:00 AM
Super User
Posts: 7,762

Re: Using a dataset values in a function

Posted in reply to silvergrenade

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 233

Re: Using a dataset values in a function

Posted in reply to silvergrenade

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;
Super User
Posts: 7,762

Re: Using a dataset values in a function

@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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 233

Re: Using a dataset values in a function

Posted in reply to KurtBremser
You're right, i misread the question.
Occasional Contributor
Posts: 11

Re: Using a dataset values in a function

[ Edited ]

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.

 

 

Super User
Posts: 7,762

Re: Using a dataset values in a function

Posted in reply to silvergrenade

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

Re: Using a dataset values in a function

Posted in reply to KurtBremser

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

Super Contributor
Posts: 474

Re: Using a dataset values in a function

[ Edited ]
Posted in reply to KurtBremser

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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