As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Where Am I? What Have I Done?

From sasCommunity
Jump to: navigation, search
Where Am I?
What Have I Done?

Jack Hamilton, Division of Research, Kaiser Permanente

Where Am I? What Have I Done?

Presentation by JackHamilton at BASAS, June 2015.

File:Where Am I.pdf

What Problem Am I Trying To Solve?

Three problems, actually!�

  • What progress is my running job making?�
  • How long have different parts of my job taken to run in the past?�
  • Which parts of the job had problems?

Macro Source

%macro Pgm_Status_File_Write(
    text=       ,   /* Text to write to program status file.        */
    outfile=    ,   /* Where to write it.                           */
    returnvalue=    /* If context needs to have a value returned,   */
                    /* put it here.                                 */ 
    );
 
    /* Make sure there are no name collisions with these global macro vars. */
    %global PGM_STATUS_LAST_DTM PGM_STATUS_OUTFILE;
    %local fileref rc fid prefix thisdtm thisdate thistime elapsed elapsedt init title;
 
    /* If last_dtm hasn't been set because this is the first call, set it. */
    %if %length(&PGM_STATUS_LAST_DTM.) = 0 %then
        %do;
        %let init = 1;
        %let PGM_STATUS_LAST_DTM = %sysfunc(datetime(), datetime20.1);
        %let PGM_STATUS_LAST_DTM = %substr(&PGM_STATUS_LAST_DTM, 1, 9) %substr(&PGM_STATUS_LAST_DTM, 11, 10);
        %put INFO: Status Log Start time initialized to &PGM_STATUS_LAST_DTM.;
        %end;
    %else
        %let init = 0;
 
    /* If output file name hasn't been set or has changed, save it.  */
    %if %length(&PGM_STATUS_OUTFILE.) eq 0 or (%length(&OUTFILE.) > 0 and &OUTFILE. ne &PGM_STATUS_OUTFILE.) %then
        %do;
        %let init = 1;
        %if %length(&OUTFILE.) > 0 %then
            %let PGM_STATUS_OUTFILE = &OUTFILE.;
        %else
            %let PGM_STATUS_OUTFILE = "./pgm_status.txt";
        %put INFO: Status Log File is &PGM_STATUS_OUTFILE.;
        %end;
 
    %let fileref = ;
 
    /* Generate fileref name and assign to file.   */
    %let rc = %sysfunc(filename(fileref, &PGM_STATUS_OUTFILE.));
 
    /* Open file    */
    %let fid = %sysfunc(fopen(&FILEREF., a));
 
    /* Write the text to the file, then close it.  */
    %if &FID. > 0
    %then
        %do;
        /* There is an oddity in the elapsed time calculation.  %SYSEVALF seems */
        /* to truncate after the first decimal place, so I just truncate at the */
        /* beginning.  Suggests on how to fix this are welcome!                 */
 
        /* Date and time now                */
        %let thisdtm = %sysfunc(datetime(), datetime20.1) ;
        /* Slightly more readable format.   */
        %let thisdatetime = %substr(&THISDTM, 1, 9) %substr(&THISDTM, 11, 10);
 
        /* Calculated elapsed time.  This is where truncation happens.  */
        %let elapsedt = %sysevalf("&THISDTM."dt - "&PGM_STATUS_LAST_DTM."dt);
 
        /* Beginning of output string.  This is what you would change if you    */
        /* want different control information.                                  */
        /* SYSCC is formatted as 4 digits so text won't wander back and forth   */
        /* if there's a 2 or 3 or 4 digit value.                                */
        %let prefix = &THISDATETIME. %sysfunc(putn(&ELAPSEDT., time11.1)) %sysfunc(putn(&SYSCC., z4.0));
 
        /* Write header line if needed.     */
        %if &INIT. %then
            %do;
            %let title = Date      Time         Elapsed   CC   Text;
            %let rc = %sysfunc(fput(&FID., &TITLE.));
            %let rc = %sysfunc(fwrite(&FID.));
            %end;
 
        /* Write to buffer                  */
        %let rc = %sysfunc(fput(&FID., &PREFIX. &TEXT.));
 
        /* And then write buffer to file    */
        %let rc = %sysfunc(fwrite(&FID.));
 
        /* And then close the file.         */
        %let rc = %sysfunc(fclose(&FID.));
 
        /* Also write the same lines to the SAS Log.    */
        %if &INIT. %then
            %put INFO: Status Log &TITLE.;
        %put INFO: Status Log &PREFIX. &TEXT.;
        %end;
    %else
        %do;
        %put ERR%str()OR: Status File: %sysfunc(sysmsg());
        %put &=SYSERR &=SYSERRORTEXT;
        /* Hope we never see that message.  */
        %return;
        %end;
 
    /* Clear the fileref.   */
    %let rc = %sysfunc(filename(fileref));
 
    /* Reset last_dtm for use in next elapsed time calculation. */
    %let PGM_STATUS_LAST_DTM = &THISDATETIME. ;
 
    &RETURNVALUE.
 
%mend Pgm_Status_File_Write;

Examples

/* These three lines will reset the environment for testing.  */
%let syscc = 0;
%let PGM_STATUS_OUTFILE =;
%let PGM_STATUS_LAST_DTM =;
 
 
/* Simple example   */
%Pgm_Status_File_Write(
    text=hello there
    );
%Pgm_Status_File_Write(
    text=hello here?
    );
 
/* Another example with explicit file name. */
%Pgm_Status_File_Write(
    text=Hello again        ,
    outfile="n:\sas\status.txt"
    );
%Pgm_Status_File_Write(
    text=Hello once more)
 
 
/* Example using CALL EXECUTE in data step.     */
data _null_;
    set sashelp.class (obs=4);
    length text $200.; drop text;
    text = catt('Execute Obs=',  _n_, ' name=', name);
    call execute('%pgm_status_file_write(text=' || trim(text) || ')' );
    call sleep(1, .45);
run;
 
 
/* Another example using more observations.  */
%let PGM_STATUS_OUTFILE =;
%let PGM_STATUS_LAST_DTM =;
 
data _null_;
    set sashelp.prdsale (obs=200) end=nomore;
    length text $200.; drop text;
    text = catt('Execute Obs=',  _n_);
    call execute('%pgm_status_file_write(text=' || trim(text) || ')' );
    call sleep(1, .01);
run;
 
 
/* Conditional execution.   */
data _null_;
    set sashelp.prdsale end=nomore;
    length text $200.; drop text;
    if _n_ = 1 or mod(_n_, 500) = 0 or nomore then
        do;
        text = catt('Execute Obs=',  _n_);
        call execute('%pgm_status_file_write(text=' || trim(text) || ')' );
        end;
run;
 
 
/* Use Resolve instead of call execute. */
data _null_;
    set sashelp.class (firstobs=5 obs=8);
    length text $200.; drop text;
    text = catt('Resolve Obs=',  _n_, ' name=', name);
    length rc $8.;  drop rc;
    rc = resolve('%pgm_status_file_write(text=' || trim(text) || ')' );
    call sleep(1, .45);
run;
 
 
/* Can also use RESOLVE in PROC SQL.    */
%let syscc = 0;
%let PGM_STATUS_OUTFILE =;
%let PGM_STATUS_LAST_DTM =;
 
proc sql inobs=3;
    create table stuff (drop=_dummy) as
        select
            *,
            resolve('%pgm_status_file_write(text=' ||
                        catt('SQL Res Loop=',  monotonic(),' name=', name) ||
                        ')')
                as _dummy length=1
        from
            sashelp.class
        ;
quit;
 
%Pgm_Status_File_Write(
    text=SQL Done
    );
 
 
/* Another example. */
proc sql stimer;
 
    /* This shows that records are pulled in in physical order. */
    create table demo1 (drop=_dummy) as
        select
            zip     ,
            city    ,
            resolve('%pgm_status_file_write(text=' ||
                        catx(' ', 'SQL Res Loop=',  monotonic(), 'zip=', zip, 'city=', city) ||
                        ')')
                as _dummy length=1  ,
            monotonic() as seq
        from
            sashelp.zipcode (obs=200);
 
    /* This shows that sorting happens after the pull.  */
    create table demo2 (drop=_dummy) as
        select
            zip     ,
            city    ,
            resolve('%pgm_status_file_write(text=' ||
                        catx(' ', 'SQL Res Loop=',  monotonic(), 'zip=', zip, 'city=', city) ||
                        ')')
                as _dummy length=1  ,
            monotonic() as seq
        from
            sashelp.zipcode (obs=200)
        order by
            city;
 
    /* This shows that WHERE is applied before data are read into program,  */
    /* at least in this simple example.                                     */
    create table demo3 (drop=_dummy) as
        select 
            name    ,
            age     ,
            resolve('%pgm_status_file_write(text=' ||
                catx(' ', 'name=', quote(name), 'age=', age, 'sex=', sex) ||
                ')')
                as _dummy length=1 
    from
        sashelp.class
    where
        sex = 'M';
 
 
    /* Conditional execution */
    create table demo3 (drop=_dummy) as
        select 
            name    ,
            age     ,
            case sex 
                when 'M' then
                    resolve('%pgm_status_file_write(text=' ||
                        catx(' ', 'name=', quote(name), 'age=', age, 'sex=', sex) ||
                        ')')
                else ' '
                end 
                as _dummy length=1 
    from
        sashelp.class;
 
 
    /* What happens in correlated subquery? */
    create table sex_title (sex char(1), sex_title char(7));
    insert into sex_title values('M', 'Male');
 
    /* Without tracing */
    create table demo4 as 
        select 
            name    ,
            age     ,
            (select 
                sex_title 
            from 
                sex_title as inner 
             where 
                inner.sex = outer.sex
            ) as sex_title length=7
    from
        sashelp.class as outer;
 
    /* With tracing */
    create table demo5 (drop=_dummy) as 
        select 
            name    ,
            age     ,
            resolve('%pgm_status_file_write(text=' ||
                catx(' ', 'Outer name=', quote(name), 'sex=', sex) ||
                ')')
                as _dummy length=1  ,
            (select 
                sex_title ||
                resolve('%pgm_status_file_write(text=' ||
                    catx(' ', '%str(   )Inner name=', quote(name), 'sex_title=', sex_title) ||
                    ')')
                    as sex_title length=7
                /* Have to concatenate sex_title and result of resolve function because     */
                /* in SAS's version of SQL only one value can be returned from subquery.    */
            from 
                sex_title as inner 
             where 
                inner.sex = outer.sex
            ) as sex_title
    from
        sashelp.class as outer;
 
quit;