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

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 Smiley Sad

 

Thanks for you help 😉

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

 

View solution in original post

11 REPLIES 11
Astounding
PROC Star

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.

PaigeMiller
Diamond | Level 26

@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 Smiley Sad

 


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.

 

 

--
Paige Miller
Kurt_Bremser
Super User

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.

azertyuiop
Quartz | Level 8

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 ?

Kurt_Bremser
Super User

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.

ChrisBrooks
Ammonite | Level 13

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;
PaigeMiller
Diamond | Level 26

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


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.

--
Paige Miller
azertyuiop
Quartz | Level 8

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

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Kurt_Bremser
Super User

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.

 

azertyuiop
Quartz | Level 8

Hello / Good morning ,

@PaigeMiller

 

Okay , I notice this information in my notepad . Man Wink

 

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

 

Thanks for the solution . Man Happy

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 11 replies
  • 9756 views
  • 4 likes
  • 5 in conversation