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

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

Sheeba — Fri, 07 Oct 2016 20:10:47 GMT

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

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

ballardw — Fri, 07 Oct 2016 20:27:50 GMT

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.

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

Astounding — Fri, 07 Oct 2016 20:40:44 GMT

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;

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

Sheeba — Fri, 07 Oct 2016 23:05:27 GMT

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

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

Sheeba — Fri, 07 Oct 2016 23:09:15 GMT

Hi Astounding,

Thanks for the reply.

I am planning to try with call execute .

Thanks again for the suggestion.

Regards,

Sheeba

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

Patrick — Sat, 08 Oct 2016 03:00:10 GMT

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.

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

Kurt_Bremser — Sat, 08 Oct 2016 08:53:24 GMT

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.

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

Sheeba — Mon, 10 Oct 2016 16:20:36 GMT

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

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

Sheeba — Mon, 10 Oct 2016 16:29:47 GMT

Hi KurtBremser,

Thanks a lot for the reply.

I will modify the code to include the changes.

Regards,

Sheeba