Date:         Thu, 2 Sep 2010 09:50:52 -0500
Reply-To:     "Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Data _null_;" <iebupdte@GMAIL.COM>
Subject:      Re: Monitor a spooling file
Comments: To: Proc Me <procme@concept-delivery.com>
In-Reply-To:  <201009020916.o822bV3O018704@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

Using the idea propose by the other responder a data step similar to
this should get you what you need.

You do need to re exeute the DIR command to get new data, after
sleeping.  That can be done with FILEVAR and two DIR commands.  One to
run against the file of interest and another to close that so you can
reopen it.  The @'string' input statement makes it easy to find the
file size.  INFILE statement EOF= is needed over END= because the way
I used @'string' will cause a LOSTCARD "error".  LOSTCARD is is
avoided by the implied GOTO using EOF=.

data _null_;
   Threshold = 1e4;
   put 'NOTE: ' Threshold=;
   do _n_ = 1 to 3 until(bytes gt threshold);
      do file = 'xyjoin.sas','zz.sas'; *file of interest, another file;
         filevar = catx(' ','Dir',quote(strip(file)));
         infile dummy pipe filevar=filevar eof=eof;
         if file = 'xyjoin.sas' then do;
            do while(1);
               input @'File(s)' bytes:comma12. ;
               end;
            eof:
               putlog 'NOTE: ' BYTES=;
               if bytes lt threshold then call sleep(2,1);
            end;
         end;
      end;
   stop;
   run;


On 9/2/10, Proc Me <procme@concept-delivery.com> wrote:
> Using SAS 9.2 on Windows I'm extracting a large data set from Teradata using
> Proc SQL pass-through.
>
> I'd like to be able to monitor the progress of the SQL step using a second
> SAS session: when the query stops executing and starts spooling I can kick
> off a second query, as the DBMS resources are released.
>
> I can manually monitor the dataset that is created by Proc SQL using Windows
> Explorer: when the query is executing the file size is reported as 1KB (the
> file is created and awaiting input), once the query has finished executing
> the file size changes as data is spooled to the data set.
>
> What I would like to do is use a null DATA Step to check the file size, if
> it is above a threshold, then stop and do something else (send me an email,
> kick off the next job, etc.), otherwise sleep for a minute and then try
> again. Stop after a couple of hours.
>
> I envisaged something like this:
>
> %let threshold = 2048; /* quit when size over 2kb */
> %let max_sleep = 7200; /* quit after two hours    */
> filename monitor 'c:\temp\file_to_monitor.sas7bdat';
> data _null_;
>   slept = 0;
>   file_id = fopen('monitor');
>   if file_id ne 0 then do;
>      size = finfo(file_id, 'File Size (bytes)');
>      do i = 1 by 1;
>         if size  gt &threshold then leave;
>         if slept gt &max_sleep then leave;
>         slept + sleep(60);
>         size = finfo(file_id, 'File Size (bytes)');
>      end;
>      closed = fclose(file_id);
>      if slept gt 7200 then call symput('monitor_status','Timeout');
>      else call symput('monitor_status','Spooling');
>   end;
>   else call symput('monitor_status','Error');
>   stop;
> run;
> %put &monitor_status;
>
> The problem is that fopen() returns an error, saying that the file is
> locked, is there an alternative way of getting file details without opening
> the file?
>
> Any help or suggestions would be warmly welcomed!
>
> Proc Me
>