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
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.
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
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;
Hi Astounding,
Thanks for the reply.
I am planning to try with call execute .
Thanks again for the suggestion.
Regards,
Sheeba
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.
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
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.
Hi KurtBremser,
Thanks a lot for the reply.
I will modify the code to include the changes.
Regards,
Sheeba
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.