BookmarkSubscribeRSS Feed
Sheeba
Lapis Lazuli | Level 10

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

8 REPLIES 8
ballardw
Super User

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.

Sheeba
Lapis Lazuli | Level 10

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

Astounding
PROC Star

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;

 

 

Sheeba
Lapis Lazuli | Level 10

Hi Astounding,

 

Thanks for the reply.

 

 

I am planning to try with call execute .

 

Thanks again for the suggestion.

Regards,

Sheeba

Patrick
Opal | Level 21

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.

Sheeba
Lapis Lazuli | Level 10

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

Kurt_Bremser
Super User

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.

Sheeba
Lapis Lazuli | Level 10

Hi KurtBremser,

 

Thanks a lot for the reply.

 

I will modify the code to include the changes.

 

Regards,

Sheeba

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 5068 views
  • 5 likes
  • 5 in conversation