topic How to shorten the run time in SAS Programming

How to shorten the run time

shlomiohana — Thu, 22 Oct 2020 21:40:34 GMT

Hello,

In my program I run the following code:

data days_1;
do day=1 to 10000;
output;
end;
run;

proc sql;
create table test as
select rent.date,day
from sashelp.rent, days_1
order by date,day;
quit;

In practice, my table has 2200 different dates and not 10 as in the 'rent' table, the run time is very long, looking for a way that the run time will be shorter without Cartesian multiplication or another solution.

Thanks.

Re: How to shorten the run time

SASKiwi — Thu, 22 Oct 2020 21:58:20 GMT

Not sure what your output should be but maybe something like this?

data days_1;
set sashelp.rent;
do day=1 to 10000;
output;
end;
run;

Re: How to shorten the run time

ChrisNZ — Fri, 23 Oct 2020 03:54:11 GMT

> without Cartesian multiplication

Well, you are asking for a Cartesian product...

To speed up IOs on this very simple program, look at increasing BUFSIZE and BUFNO, and using SGIO.

Re: How to shorten the run time

mkeintz — Fri, 23 Oct 2020 04:11:33 GMT

You can do the cartesian crossing via a hash object in a data step:


data want;
  set sashelp.rent  days_1 (obs=0);
  if _n_=1 then do;
    declare hash h (dataset:'days_1',ordered:'a');
	  h.definekey('date');
	  h.definedata(all:'Y');
	  h.definedone();
	declare hiter hi ('h');
  end;
  do while (hi.next()=0);
    output;
  end;
run;

This loads DAYS_1 into the hash object (think lookup table) named h, and stores it in ascending order by DAY (even if the original dataset was not sorted). This takes place during the first iteration of the data step ("if _n_=1"). The hash object is "retained" for all susequent iterations.

Then for each obs in sashelp.rent, the program uses the hash iterator hi to step through (i.e. retrieve) every dataitem (think "row") in h, and outputs it.

The reason I used "obs=0" in the SET statement for the days_1 dataset is to force SAS to make provision for all the variables in days_1 in the program data vector (the hash object declare statement won't suffice). But not to actually read in the data (which IS done in the declare statement).