Desktop productivity for business analysts and programmers

call a macro inside a proc sql

Accepted Solution Solved
Reply
Highlighted
Occasional Contributor
Posts: 16
Accepted Solution

call a macro inside a proc sql

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


Accepted Solutions
Solution
Tuesday
Super User
Posts: 7,462

Re: call a macro inside a proc sql

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 5,370

Re: call a macro inside a proc sql

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.

Trusted Advisor
Posts: 1,796

Re: call a macro inside a proc sql

[ Edited ]

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.

 

 

Super User
Posts: 7,462

Re: call a macro inside a proc 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.

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

Re: call a macro inside a proc sql

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 ?

@KurtBremser

How you can propose to get around the problem to include an instruction in the proc sql , to call the macro ?

Super User
Posts: 7,462

Re: call a macro inside a proc sql

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 397

Re: call a macro inside a proc sql

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;
Trusted Advisor
Posts: 1,796

Re: call a macro inside a proc sql

[ Edited ]

@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 @KurtBremser and @ChrisBrooks have said. Ignoring their advice would be a huge mistake.

Occasional Contributor
Posts: 16

Re: call a macro inside a proc sql

Hello / Good afternoon ,

@KurtBremser

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

Trusted Advisor
Posts: 1,796

Re: call a macro inside a proc sql


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.

Solution
Tuesday
Super User
Posts: 7,462

Re: call a macro inside a proc sql

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.

 

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

Re: call a macro inside a proc sql

Hello / Good morning ,

@PaigeMiller

 

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

 

@KurtBremser

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

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 200 views
  • 4 likes
  • 5 in conversation