Parameterizing Proc SQL with Arrays and Iterating Using Nested Loops

[DanielSantos]

Yes.

If the number of iterations are pretty high you may have reached some limit.

Try to reformulate your macro in the following way:

%macro iterate;
%do u=1 %to &cntunits;
%do s=1 %to &cntservices;
proc sql noprint;
insert into InitialTickets
select
orig.unit_pk,
orig.service_cat_desc,
orig.so_pk,
min(orig.sub_prod_pckg_pk) as pk
from
conx.subscriber_services as orig
where
orig.so_pk is not null and
orig.unit_pk = &&Units&u and
orig.service_cat_desc = &&Services&s
group by
orig.unit_pk,
orig.service_cat_desc,
orig.so_pk
;
quit;
%end;
%end;
%mend iterate;

It will do the same but issue and commit one proc sql statement per iteration.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[StephenOverton]

There is a large number of iterations, about 500K total. I reformulated the macro as you describe to perform the proc sql once per iteration and it seems to work right now. Its been running for about 10min which is longer than the normal 3min. So I might have a solution. more


Message was edited by: SteveONCSU

[StephenOverton]

It must be working now, still running after 45min.

[StephenOverton]

Success! Everything works now thanks to Daniel's suggestion. Took about an hour to complete.

[StephenOverton]

One last thing, the log file is HUGE now. Anybody know how to stop proc sql from writing to the log??

[DanielSantos]

Hello.

First try settting/unsetting some of the options to control the level of detail messages that are written into the log.

Check here for info: http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001906177.htm

Then, if this doesn't suit your needs, you could still try a more radical approach that is to redirect the log to a dummy file.

See the PRINTTO procedure: http://support.sas.com/documentation/cdl/en/hosto390/61886/HTML/default/mvs-sasout-printto.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[claudia]

Just to let you know that I had a similar problem and thanks to all of your comments and suggestions I could successfully solve it!

Thanks!! 😃