DATA Step, Macro, Functions and more

How to execute a macro for each record in a sas dataset in parallel?

Reply
Regular Contributor
Posts: 162

How to execute a macro for each record in a sas dataset in parallel?

Hi,

 

I have a control table which has a list of countries. I am calling a macro test for each value of the country using call execute function.

 

If the field country has three values , is it possible to modify the code so as to make three macro calls run in parallel?

 

%macro test(country);

    data out_&country;
        set dataset1;
        where country = &country;
    run;

%mend test;

data _null_;
    set controltable;
    by country;

    if first.country;
    call execute(cats('%test(',country,')'));
run;

 

Thanks in advance,

 

Regards,

Sheeba

Super User
Posts: 10,466

Re: How to execute a macro for each record in a sas dataset in parallel?

Does your Country variable contain any names like New Guinea where there is a space imbedded in the name? Or are the values in your Country variable delimited by a special character between each name such as a comma or pipe?

 

If none contain spaces then

data _null_;
    set controltable;
    by country;
    length cname $ 30 ;
    if first.country then do i = 1 to countw(country);
      cname = scan(country,i);
      call execute(cats('%test(',cname,')'));
    end;
run;

may work. If you have a delimeter betwee names then that goes in the COUNTW and SCAN function calls.

 

 

You may want this line:

        where country = &country;

to be

        where index(country,"&country")>0;

because other wise your single country in the call won't match the value with multple names in the country variable.

Regular Contributor
Posts: 162

Re: How to execute a macro for each record in a sas dataset in parallel?

Hi Ballardw,

 

 

Thanks for the reply.

 

Sure . I will include the delimiter.

 

Thanks for pointing out the index function.

 

Also if the values of the country are as follows

 

country

country1

country2

country3

country4

 

is there anyway i can call the macro for each record parallelly.

 

that is for each record can i make some modification to execute the macro for each record parallelly so as to reduce the time taken ?

 

 

Thanks again,

Regards,

Sheeba

Super User
Posts: 5,074

Re: How to execute a macro for each record in a sas dataset in parallel?

It depends on what you are really after here.  If you want practice with CALL EXECUTE, that's one thing.  But if you just want to extract all the countries that match into one large data set, there are many ways to accomplish that.  Here's one (subject to debugging, since my SQL is not necessarily correct):

 

proc sql;

create table selected_countries as select * from dataset1

  where country in (select country from control_table);

quit;

 

 

Regular Contributor
Posts: 162

Re: How to execute a macro for each record in a sas dataset in parallel?

Hi Astounding,

 

Thanks for the reply.

 

 

I am planning to try with call execute .

 

Thanks again for the suggestion.

Regards,

Sheeba

Respected Advisor
Posts: 3,887

Re: How to execute a macro for each record in a sas dataset in parallel?

[ Edited ]

is there anyway i can call the macro for each record parallelly.

that is for each record can i make some modification to execute the macro for each record parallelly so as to reduce the time taken ?

 

If you have SAS/Connect licensed then you could run each table creation in a separate rsubmit block in parallel, but....

 

If you're really concerned about performance then the first thing you should do is to reduce passes through your large table. The way you've coded there would be a full pass through the large table for every single country.

 

Below a code version where you get the same result with a single pass through your large table.

data controltable;
  length country $ 10;
  do country='CANADA', 'EGYPT', 'GERMANY', 'MEXICO';
    output; output;
  end;
  stop;
run;

proc sql noprint;
  select 
    distinct catt('work.',country) 
      into :country_tbls separated by ' '
  from controltable
  ;

  select 
    distinct catt('when("',country,'") output work.',country,';') 
      into :country_selection separated by ' '
  from controltable
  ;
quit;

data &country_tbls;
  set sashelp.prdsale;

  select(country);
    &country_selection
    otherwise;
  end;
run;

And last but not least: It's most of the time a sub-optimal design approach to split up a single data set into multiple data sets the way you're doing it. Most of the time it's much easier to keep the data "together" and use by group processing.

Regular Contributor
Posts: 162

Re: How to execute a macro for each record in a sas dataset in parallel?

Hi Patrick,

 

Thanks a lot for the detailed reply.

 

I am new to SAS/connect. I will go through the details.

 

Thanks a lot for pointing out the passthrough. I will change and incorporate the below version/

 

Regards,

Sheeba Swaminathan

Super User
Posts: 6,928

Re: How to execute a macro for each record in a sas dataset in parallel?

[ Edited ]

Rather than creating a macro that runs the analysis for one item, I'd rather use call execute to develop one analysis step that outputs the results in one pass:

data _null_;
call execute("data");
do until (eof1);
  set countries end=eof1;
  call execute(" country_"!!trim(country));
end;
call execute("; ");
do until (eof2);
  set countries end=eof2;
  call execute("/* analysis code for one country */; output country_"!!trim(country)!!"; ");
end;
call execute("run;");
run;

(completely untested, of course)

This would create a multiple of outputs in one scan through the original dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 162

Re: How to execute a macro for each record in a sas dataset in parallel?

Hi KurtBremser,

 

Thanks a lot for the reply.

 

I will modify the code to include the changes.

 

Regards,

Sheeba

Ask a Question
Discussion stats
  • 8 replies
  • 394 views
  • 5 likes
  • 5 in conversation