How to pass a macro variable through tranwrd function for each data row?

[ScoobieR]

Hi there

As a simple example , I have a data set that looks like below - the question the person gets asked depends on other criteria, and at the end I want to populate the person's name into question2 instead of the dummy tag.

I've tried using tranwrd and this works for part of my problem - but it replaces {{lead.FirstName}}  in Question2 to say "Tom" for every row as this is the last name in my data set. 

the code I am using is (in Base SAS 9.4):

data test;
set test;
call symput('FirstName2',trim(FirstName));
question2 = (tranwrd(question,"{{lead.First Name}}","&FirstName2.");
run;

Please could someone help me on how I get the correct name populated in each data row? have been searching and trying things for hours but can't find anything.

FirstName	Question	Question2 (what I want it to say)	Question2 (what it actually does)
Caroline	{{lead.First Name}}, do you have a red car?	Caroline, do you have a red car?	Tom, do you have a red car?
Mike	{{lead.First Name}}, do you have a blue car?	Mike, do you have a blue car?	Tom, do you have a blue car?
Eden	{{lead.First Name}}, do you have a green car?	Eden, do you have a green car?	Tom, do you have a green car?
Tom	{{lead.First Name}}, do you have a black car?	Tom, do you have a black car?	Tom, do you have a black car?

[ballardw · Accepted solution]

Why are you using a macro variable at all?

Since you want to use the current value of Firstname to replace text on that record there is not reason to create a macro variable.

Did you try some

question2 = tranwrd(question,"{{lead.First Name}}", strip(FirstName) );

[Tom]

SAS resolves the macro code/references before it compiles and runs the data step.

So the first time you ran it it would have generated this value instead.

&Firstname2., do you have a red car?

And written this note into the log:

WARNING: Apparent symbolic reference FIRSTNAME2 not resolved.

It is only after you had run the step at least once that FIRSTNAME2 had a value and of course it just has the value from the last observation as at each iteration of the data step the macro variable's value is getting changed.

Do not use a macro variable for this problem as there is nothing about it that requires you to generate any code.

Just use the value that is already in the dataset!!

question2 = tranwrd(question,'{{lead.First Name}}',trim(FirstName)) ;

[ScoobieR]

Thank you Tom and BallardW - I don't know why I didn't try this - I thought the last parameter of tranwrd had to be in quotes! Clearly been looking at it too long.

Both great and helpful replies - thank you.

[ballardw]

---

@ScoobieR wrote:

Thank you Tom and BallardW - I don't know why I didn't try this - I thought the last parameter of tranwrd had to be in quotes! Clearly been looking at it too long.

Both great and helpful replies - thank you.

---

The quotes would be needed if I want to set a specific value, not a variable, for all records. Such as

tranwrd (schoolname, 'HS', 'High School');

The requirement is that the result be a character value. You can even do operations such as concatenating a string and a variable, or multiple variables, or the substr of a variable or ...