Hello ,
I want call a macro inside à proc sql like this :
%macro changecharac (value=); value=tranwrd(value,'&','and'); value=tranwrd(value,'N/A','empty'); %mend changecharac; proc sql; create table my_table as select field1 , field2 , %changecharac(value=field3) as field3sec from my_table_origin; quit;
What you think about this ?
SAS doesn't read the code of macro
Thanks for you help 😉
So the code you should test if it's working on its own should be
proc sql;
create table my_table as
select
field1,
field2,
case
when field3 like '%é%' then tranwrd(field3,'é','e')
when field3 like '%è%' then tranwrd(field3,'è','e')
else tranwrd(field3,'à','a')
end as field3sec
from my_table_origin
;
quit;
although I have the inkling that this would be better:
proc sql;
create table my_table as
select
field1,
field2,
translate(field3,'eea','éèà') as field3sec
from my_table_origin
;
quit;
as you only scan for single characters, while tranwrd is specifically meant for multi-character strings.
Next, we'd identify code pieces to be made flexible and replace them with macro vars:
%let value=field3;
proc sql;
create table my_table as
select
field1,
field2,
translate(&value,'eea','éèà') as &value.sec
from my_table_origin
;
quit;
Once that works, we finally extract our flexible code into a macro definition and use that:
%macro changecharac (value=);
translate(&value,'eea','éèà') as &value.sec
%mend;
proc sql;
create table my_table as
select
field1,
field2,
%changecharac(value=field3)
from my_table_origin
;
quit;
Note that the code inside the macro is a one-to-one replica of the code we used in step 2.
Let's start here. What would you expect this program to do?
proc sql;
create table my_table as
select field1, field2,
value = tranwrd(value, '&', 'and');
value = tranwrd(value, 'N/A', 'empty');
as field3sec
from my_table_origin;
quit;
Your answer will give us some basis to even discuss the question of why your macro has difficulty.
@azertyuiop wrote:
Hello ,
I want call a macro inside à proc sql like this :
%macro changecharac (value=); value=tranwrd(value,'&','and'); value=tranwrd(value,'N/A','empty'); %mend changecharac; proc sql; create table my_table as select field1 , field2 , %changecharac(value=field3) as field3sec from my_table_origin; quit;What you think about this ?
SAS doesn't read the code of macro
SAS does read the code inside the macro and it finds errors.
Your statements inside the macro are not SQL statements and so they don't work inside of SQL. You have to write valid SQL statements — in this case, you have to write fragments of SQL statements that fit inside of a SELECT statement in PROC SQL.
But let's be real ... you don't need a macro of any sort here. Just include the tranwrd functions inside SQL without macros. Macros in this case are not adding value, they don't do anything you couldn't do in plain old SQL.
Aside from what the others already stated, you define your macro with a parameter (value), but you never use it in the macro. The text value is not a reference to a macro parameter.
Hello / Good morning ,
@Astounding
For the "field3" in my table I want change severals values. For example when I found "&" or "n/a" .
@PaigeMiller
I can replace the macro by severals macros ? For example a macro to replace "&" and a second macro to replace "n/a" ? It's possible to cut the macro in severals macros ?
@Kurt_Bremser
How you can propose to get around the problem to include an instruction in the proc sql , to call the macro ?
What the other two meant:
The macro preprocessor is a code generator. Its results are inserted into the code in place of the macro, and then the main SAS interpreter tries to run that.
The correct path of macro development is this:
- write code that works (no macro usage involved yet)
- identify parts that need to be made flexible
- replace the flexible parts with macro variables, set these beforehand, and verify that the code still works
- wrap the code in a macro definition, and supply the variables from step 3 as macro parameters
- test with parameters
Your first step must now be to write your SQL:
proc sql;
create table my_table as
select field1 , field2 , /* insert correct SQL code here */ as field3sec
from my_table_origin;
quit;
so that it works. At this point in time, you only need to know how to write correct SQL syntax.
Once you've done that, follow the other steps of macro development, and report back here when you have problems with one of them.
I don't think you need a macro for this, you can do it with a CASE statement. You haven't given us sample data so I've created an example using SASHELP.CARS which you should be able to adapt for your purpose.
proc sql;
create table new_cars
as select make, case when model contains '4dr' then tranwrd(model,'4dr','four door')
when model contains '2dr' then tranwrd(model,'2dr','two door')
else model end
from sashelp.cars;
quit;
I can replace the macro by severals macros ? For example a macro to replace "&" and a second macro to replace "n/a" ? It's possible to cut the macro in severals macros ?
Let me waste a few electrons here and repeat what I have said and what others have said. MACROS ARE NOT NEEDED HERE given what you have told us. Important point: we are not recommending that you "replace the macro by several macros". We are recommending that you use ZERO macros.
In particular please pay attention to @Kurt_Bremser and @ChrisBrooks have said. Ignoring their advice would be a huge mistake.
Hello / Good afternoon ,
@Kurt_Bremser
On the SAS code that you propose , you say " /* insert correct SQL code here */ " . In my case to do not use a macro and include a SQL code I can replace by this :
/* CASE field3 WHEN field3 like '%é%' THEN tranwrd(field3,'é','e') WHEN field3 like '%è%' THEN tranwrd(field3,'è','e') ELSE tranwrd(field3,'à','a') END */
@ChrisBrooks
Excatly it's the solution that I have thinked in first juste before to write a code with a macro.
@PaigeMiller
For you in my case to use a maco isn't necessary and it's impossible ?
I must concluded that " tranwrd " can't use with a macro in a SAS programm ?
Thanks for your help
@azertyuiop wrote:
Hello / Good afternoon ,
@PaigeMiller
For you in my case to use a maco isn't necessary and it's impossible ?
I must concluded that " tranwrd " can't use with a macro in a SAS programm ?
No one used the word "impossible". It is possible. It is possible, but a very poor practice in this case, and unnecessary thing to do.
You conclusion is incorrect. TRANWRD can be used in a macro.
So the code you should test if it's working on its own should be
proc sql;
create table my_table as
select
field1,
field2,
case
when field3 like '%é%' then tranwrd(field3,'é','e')
when field3 like '%è%' then tranwrd(field3,'è','e')
else tranwrd(field3,'à','a')
end as field3sec
from my_table_origin
;
quit;
although I have the inkling that this would be better:
proc sql;
create table my_table as
select
field1,
field2,
translate(field3,'eea','éèà') as field3sec
from my_table_origin
;
quit;
as you only scan for single characters, while tranwrd is specifically meant for multi-character strings.
Next, we'd identify code pieces to be made flexible and replace them with macro vars:
%let value=field3;
proc sql;
create table my_table as
select
field1,
field2,
translate(&value,'eea','éèà') as &value.sec
from my_table_origin
;
quit;
Once that works, we finally extract our flexible code into a macro definition and use that:
%macro changecharac (value=);
translate(&value,'eea','éèà') as &value.sec
%mend;
proc sql;
create table my_table as
select
field1,
field2,
%changecharac(value=field3)
from my_table_origin
;
quit;
Note that the code inside the macro is a one-to-one replica of the code we used in step 2.
Hello / Good morning ,
@PaigeMiller
Okay , I notice this information in my notepad .
@Kurt_Bremser
I have read your solution and the better choice in my case it's the last :
%macro changecharac (value=); translate(&value,'eea','éèà') as &value.sec %mend; proc sql; create table my_table as select field1, field2, %changecharac(value=field3) from my_table_origin ; quit;
Excellent, i notice this in my notepad too .
Thanks for the solution .
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.