topic Macro in SAS Programming

Macro

Ronein — Sun, 05 Aug 2018 07:42:45 GMT

Hello

%macro rrjow(x,branchP);

PROC SQL;
	create table tbl_&x.  as
	select  *
	from  input_data 
	where branchP=&branchP.
;
QUIT;
%rrjow(x=1, branchP=100);
%rrjow(x=2, branchP=200);
%rrjow(x=3,branchP=300);
%rrjow(x=4,branchP=400);
%rrjow(x=5,branchP=500);

%rrjow(x=6,branchP=ALL);/*How can I run it???????*/

I want to run same piece of code again and again for different branches.

I know that for this situation the best is to use Macro.(It is saving code and save time).

Let's say that I want to run it for following branches: 100,200,300,400,500

and also I want to run it for all branches (list of 1000 branches).

My only question is how can I run the macro for all branches ???

Re: Macro

Kurt_Bremser — Sun, 05 Aug 2018 07:51:05 GMT

Put your values into a dataset and use call execute() in a data _null_ step run from that dataset.

Re: Macro

Ronein — Sun, 05 Aug 2018 08:00:14 GMT

Hello and thank you very much.

I am sorry but I didn't understand.

I need to run also for all branches together....

Re: Macro

Ronein — Sun, 05 Aug 2018 08:14:53 GMT

May you please send an example

thank you so much

Joe

Re: Macro

hashman — Sun, 05 Aug 2018 09:20:50 GMT

First, your code won't work because you define your parameters as positional, yet call the macro with keyword parameters. Your calls should look like:

%rrjow (x, branchP)

Note the absence of the semicolon after the macro call - it's not needed. Second, it won't work because you have no %MEND statement.

Third, instead of calling the macro thus defined a bunch of times, you should make the second parm accept a list of values and make a provision in the macro code for SQL to handle it. For example:

%macro rrjow (x=, branchP=) ;  
  proc sql ;                   
    create table tbl_&x as     
    select *                   
    from   input_data          
    where  branchP in (&ranchP)
    ;                          
  quit ;                       
%mend ;

So, if you want to call the macro for a single branch, you'd code:

%rrjow (x=5, branchP=500)

Or, if you want a bunch of branches:

%rrjow (x=6, branchP=100 200 300 400 500 600 700 800 900 1000)

There're other variants that may simplify calling the macro - for example, if you always use a range of branches and they're always multiple of 100, you can parameterize the first and last elements of the range and use the BETWEEN clause instead. But your inquiry isn't specific enough to go beyond what I've offered.

HTH

Paul D

Re: Macro

Kurt_Bremser — Sun, 05 Aug 2018 08:51:11 GMT

Oh , you want all those branches in one dataset. You don't need the macro for that, it's a simple join:

proc sql;
create table tbl_all as
select a.*
from input_data a
right join control b
on a.branchP = b.branchP
;
quit;

where control is a dataset that contains all your wanted branches.

Re: Macro

hashman — Sun, 05 Aug 2018 08:51:02 GMT

What @Kurt_Bremser is saying is store your parameters in a SAS file and use that control data set to generate the code you need using the CALL EXECUTE routine. This is absolutely how I would approach it in principle much rather than using the macro language (except that instead of using CALL EXECUTE I'd use a different method of generating code - but that's a matter of personal preference).

As a method of generating dynamic code, macros are grossly abused (especially in the clinical trials business), and in a number of ways (for example, from the standpoint of change control) they are vastly inferior to methods based on table-driven approaches. In time, you'll understand subtleties of this nature.

Paul D.

Re: Macro

Jagadishkatam — Sun, 05 Aug 2018 08:52:11 GMT

%macro rrjow(x,branchP);
%do x=100 %to 1000 %by 100;
PROC SQL;
	create table tbl_&x.  as
	select  *
	from  input_data 
	where branchP=&x.
;
QUIT;
%end;
%mend rrjow;
%rrjow;

Re: Macro

Kurt_Bremser — Sun, 05 Aug 2018 08:56:10 GMT

When rereading this post I found a nice formulation for this kind of misunderstanding:

You never solve something with a macro, you can only improve an existing solution.

Another expression of Maxim 11.

Re: Macro

hashman — Sun, 05 Aug 2018 09:16:13 GMT

@Kurt_Bremser:

Nicely put.

Furthermore, the very possibility of improving an existing solution with a macro depends on the structure of the solution.

I have nothing against macros per se - they're useful when they're used when and where they need to be used.

But not beyond that.

Best

Paul D.

Re: Macro

akash1088 — Sun, 05 Aug 2018 11:13:24 GMT

%rrjow(x=6,branchP=in (Select Branch from input_data ))

try this.

Re: Macro

Tom — Sun, 05 Aug 2018 17:27:02 GMT

@hashman wrote:

First, your code won't work because you define your parameters as positional, yet call the macro with keyword parameters. Your calls should look like:

Paul -

That is a false statement. You can call positional parameters by name. You just can't call named parameters by position.

Tom

Re: Macro

hashman — Sun, 05 Aug 2018 18:50:57 GMT

@Tom:

This is a true statement - on your part, that is. I may be getting signs of early macrodementia. Sad.

Paul D.

Re: Macro

ballardw — Mon, 06 Aug 2018 20:24:50 GMT

If you really want to use a key value like ALL:

%macro rrjow(x,branchP);

PROC SQL;
	create table tbl_&x.  as
	select  *
	from  input_data 
   %if &branchP ne ALL %then %do;
	where branchP=&branchP.
   %end;
;
QUIT;
%mend;

Make sure you pass the parameter value ASS as UPPERCASE or make the macro do that (though if you have character branch values that have lower case letters than gets a little bit trickier.

Do pay attention to where the semicolons go. The macro conditionals need them the end their statements. The WHERE clause in the SQL does not want the semicolon in between the %if/%end as then you would hit the QUIT instruction without ending the create table first.