BookmarkSubscribeRSS Feed
DanielSantos
Barite | Level 11
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
Fluorite | Level 6
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
Fluorite | Level 6
It must be working now, still running after 45min.
StephenOverton
Fluorite | Level 6
Success! Everything works now thanks to Daniel's suggestion. Took about an hour to complete.
StephenOverton
Fluorite | Level 6
One last thing, the log file is HUGE now. Anybody know how to stop proc sql from writing to the log??
DanielSantos
Barite | Level 11
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
Calcite | Level 5
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!! 😃

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 21 replies
  • 2426 views
  • 0 likes
  • 6 in conversation