Date:         Mon, 12 Apr 2004 13:18:57 -0400
Reply-To:     Venky Chakravarthy <venky.chakravarthy@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Venky Chakravarthy <venky.chakravarthy@PFIZER.COM>
Subject:      Re: Capturing sql statement run times in a dataset

I have used something like what Howard has suggested below. The only
difference is that I wrote directly to a SAS data set and appended repeats
of the experiment as single observation data sets. This would facilitate
some direct analysis later. Here is something extracted and edited from
something I have done before:

%macro timediff ;
   libname c "c:\c_data\junk" ;
   %do i = 1 %to 100 ;
      %let STRbegin = %sysfunc(time()) ;
      data _null_ ;
      <insert steps> ;
      run ;
      %let STRend = %sysevalf(%sysfunc(time()) - &STRbegin) ;

      %if &i = 1 %then %do ;
         data c.timelog ;
            iter     = input(&i,best.) ;
            strtime  = input(&strend,best.) ;
         run ;
      %end ;

      %else %do;
         data t ;
            iter     = input(&i,best.) ;
            strtime  = input(&strend,best.) ;
         run ;

         proc append base = c.timelog
                     data=t ;
         run ;
      %end ;
   %end ;

   <insert steps to analyze>

   libname c clear ;
%mend timediff ;

%timediff

On Sat, 10 Apr 2004 13:20:34 -0400, Howard Schreier
<Howard_Schreier@ITA.DOC.GOV> wrote:

>What I've done is capture timestamps into macrovariables before and after
>running the code I want to time, then use SQL to stuff the the timestamps
>(along with other variables which identify and characterize the experiment)
>into a table.
>
>Some code fragments:
>
>   %let starttime = %sysfunc(dhms(%sysfunc(today()),
>                                  0,
>                                  0,
>                                  %sysfunc(time())));
>
>   [code to be timed]
>
>   %let   endtime = %sysfunc(dhms(%sysfunc(today()),
>                                  0,
>                                  0,
>                                  %sysfunc(time())));
>
>   proc sql;
>
>   insert into testdir.log set
>      starttime = &STARTTIME ,
>        endtime = &ENDTIME ;
>
>   quit;
>
>On Fri, 9 Apr 2004 15:42:53 -0700, Pardee, Roy <pardee.r@GHC.ORG> wrote:
>
>>Hey All,
>>
>>I'm trying to compare query performance against sql/view and real
>>versions of a particular dataset, to see whether the space I save w/the
>>view is worth the extra cpu time it takes to query the view.
>>
>>I'd like to do a bunch of different queries & build the run times up in
>>a dataset that I can thereafter analyze.  I've been over the docs for
>>proc sql & macro, but haven't found a way to do it.  Does anyone know
>>how?  I'm on v8.2 on win2k.
>>
>>Thanks!
>>
>>-Roy
>>
>>Roy Pardee
>>Research Analyst/Programmer
>>Center For Health Studies (Cancer Research Network)
>>Group Health Cooperative
>>(206) 287-2078
>>
>>This message and any attached files might contain confidential
>>information protected by federal and state law. The information is
>>intended only for the use of the individual(s) or entities originally
>>named as addressees. The improper disclosure of such information may be
>>subject to civil or criminal penalties. If this message reached you in
>>error, please contact the sender and destroy this message. Disclosing,
>>copying, forwarding, or distributing the information by unauthorized
>>individuals or entities is strictly prohibited by law.