topic Re: SAS keeps running after it has read in all of the observations that match the subset condition in SAS Programming

SAS keeps running after it has read in all of the observations that match the subset condition

eiger — Wed, 15 May 2024 17:26:48 GMT

When I subset a dataset in a DATA step, SAS will continue to run for much longer than expected, so long in fact that I have not seen it finish running. However, when I break the run and cancel the submitted statements, the log indicates that 1,271 observations were read, which is number of observations that I expect to have in the subset. Why is it that SAS keeps running when all of the observations that match the WHERE condition have been read?

In the DATA step I use a WHERE statement to subset for observations where the character variable SUB = '123'. The dataset is large (1.3M+ obs.), but as I mentioned, the resulting data set "filtered_items" should only have 1,271 observations.

libname corpxin "\\filepath\folder";

data filtered_items;
	set corpxin.items_202001;
	where SUB = '123';
run;

Re: SAS keeps running after it has read in all of the observations that match the subset condition

Tom — Wed, 15 May 2024 17:38:11 GMT

Where is the libref pointing?

Is that an actual SAS dataset? Or is the libref pointing to some external database?

Is the member an actual dataset (or database table) or is it a view?

Is your where clause really that simple equality test? Or is it something more complex?

Note that if the libref is pointing to a remote database and the where clause is something that cannot be passed to the remote database then the delay is probably from SAS having to copy ALL of the observations to the SAS server in order to apply the filter.

Re: SAS keeps running after it has read in all of the observations that match the subset condition

eiger — Wed, 15 May 2024 17:47:33 GMT

The library is a folder on a remote drive. The file corpxin.items_202001 is a .sas7bdat dataset.

Yes, the where clause is just a simple equality test.

Re: SAS keeps running after it has read in all of the observations that match the subset condition

PaigeMiller — Wed, 15 May 2024 18:00:41 GMT

Why is it that SAS keeps running when all of the observations that match the WHERE condition have been read?

I will guess that you did not submit the run; command.

Or guess #2 — SAS is still crunching through the 1.3 million records. SAS can find the 1271 of interest and still have 1.2 million records to search through; it doesn't know there are no more matches.

Re: SAS keeps running after it has read in all of the observations that match the subset condition

eiger — Wed, 15 May 2024 18:03:06 GMT

I did include the run; command.

Re: SAS keeps running after it has read in all of the observations that match the subset condition

PaigeMiller — Wed, 15 May 2024 18:17:11 GMT

Re: SAS keeps running after it has read in all of the observations that match the subset condition

mkeintz — Wed, 15 May 2024 18:43:35 GMT

Local SAS probably doesn't know that the library is on a remote drive. That is masked by the operating system, or the remote-access file system. So the data engine (and the "where" filter) is probably running on the local system, meaning, as @Tom suggested, all the data is being transported to the client (where SAS is running) system prior to applying the filter.

Since the data file is a .sas7bat file, you would need a way to have SAS to run a data engine on the remote system to apply the filter prior to transport to the local system. I have done this with sas/connect, but that requires a SAS license on both the client and server systems.

In the absence of the above, you can at least measure your progress with something like this, which reports elapsed time for the 1st, 101st, 201st, etc. obs that passes the where filter.

data _null_;
  t0=put(time(),time8.0);
  call symput("T0",t0);
 run;
%put &=t0;

data filtered_items;
	set corpxin.items_202001;
	where SUB = '123';

  if mod(_n_,100)=1 then do;  
    t1=time();
    elapsed=t1-"&t0"t;
    put _n_=comma6.0  t1=time8.0  elapsed=time8.0;
  end;
  drop t1 elapsed;
run;

Re: SAS keeps running after it has read in all of the observations that match the subset condition

mkeintz — Wed, 15 May 2024 18:57:38 GMT

Also, if the OP is certain that only 1,271 observations satisfy the filter, the program can be made to stop after outputting those 1,271, even if there remains a large part of the data file yet to be submitted to the filter.

data filtered_items;
	set corpxin.items_202001;
	where SUB = '123';

  output;
  if _n_=1271 then stop;
run;

Re: SAS keeps running after it has read in all of the observations that match the subset condition

Tom — Wed, 15 May 2024 18:58:54 GMT

@eiger wrote:

The library is a folder on a remote drive. The file corpxin.items_202001 is a .sas7bdat dataset.

Yes, the where clause is just a simple equality test.

SAS will have to read the WHOLE file to know whether or not there are any more observations that meet the where condition.

You could probably improve the performance of that query in a couple of different ways.

You could add an index on that variable. Then SAS could read the index file (which should be much smaller) and find out which parts of the dataset need to be read.

If the dataset was sorted by that variable (and the values you want are near the start) then you could use an IF statement instead of a where statement and add a test to stop when you have passed the values of interest.

libname corpxin "\\filepath\folder";

data filtered_items;
  set corpxin.items_202001;
  by sub;
  if SUB = '123' then output;
  if sub > '123' then stop;
run;

Re: SAS keeps running after it has read in all of the observations that match the subset condition

ballardw — Wed, 15 May 2024 19:08:00 GMT

@eiger wrote:

The library is a folder on a remote drive. The file corpxin.items_202001 is a .sas7bdat dataset.

Yes, the where clause is just a simple equality test.

I am going to say a combination of the data step having to read every observation coupled with your network bandwidth or traffic.

Several years ago I copied network data sets to my local drive because the amount of time to access a data set with 5,000 observations was excessive. As in running proc freq on a couple of variables could take 15 minutes because of the amount of network traffic using that drive. Running with the local copy was a few seconds. I can image that if your data set with a million plus observations was on that drive with the network in effect that it could take hours to complete.