Conditionally writing a new observation to dataset

[hgaronfolo]

I have this table, that I use to track when my code runs, and how many times it has been run for the same snapshot date:

data run_table;
snpst_dt = &dt;
run_ts = datetime();
format run_ts ts_format.;
run_id = 1;
run;

So, if you run it twice for a snapshot date, there should be two run_ids (1 and 2). If you run it for the first time for a certain snapshot date, then the run_id should be 1. I am trying to this conditionally with the following code:

data run_table_temp;
set run_table(where=(snpst_dt=&dt));
if snpst_dt = &dt then run_id +1;
else do;
snpst_dt = &dt;
run_ts = datetime();
format run_ts ts_format.;
run_id = 1
end;
run;

If I run the above for an existing snapshot date, then the run_id indeed incremented, but if I run it for a snapshot date, that does not already exist, then I get 0 rows, instead of the expected new row (the "else do" part).

What am I missing? Does it have something to do with the output statement?

[ChrisBrooks]

I think the problem is that when there is no record for the snapshot date in the data set the where option will ensure that the SET statement returns no observations. This means that the rest of the step will not be executed.

One way around this would be to use Proc SQL to return the latest date in the data set and then use that in the where option to ensure something is returned (as long as the data set isn't entirely empty). You could then compare the data with the current date to determine what action to take. Here is a simple example using a data set from the SASHELP library to give you an idea how to proceed

/* Doesn't Work */

data _null_;
	set sashelp.air(where=(date=today()));
	put "here";
run;


/* Works */

proc sql;
	select max(date)
	into :maxdate
	from sashelp.air;
quit;

data _null_;
	set sashelp.air(where=(date=&maxdate));
	if date=today() then do;
		put date=;
	end;
	else do;
		put "New Record Needed";
	end;
run;

[gamotte]

Hello,

As explained by @ChrisBrooks, when no new record is retrieved by a set statement, SAS stops processing further instructions

and exits the data step.

You can use instead a merge statement as follows :

data test;
    format dt date9. count best.;
    stop;
run;

%macro update(ds, date);

    data newdate;
        dt=&date.;
    run;

    data &ds.;
        merge newdate (in=innew) &ds. (in=date_exists);
        by dt;

        if innew then do;
            if date_exists then count+1;
            else count=1;
        end;
    run;

%mend;

data _NULL_;
    call symputx("yesterday", intnx('day',today(),-1));
    call symputx("today", today());
run;

%put &yesterday. &today.;

%update(test, &yesterday.);
%update(test, &yesterday.);
%update(test, &today.);
%update(test, &today.);
%update(test, &today.);

[s_lassen]

One possibility is to use a MODIFY statement:

data run_table;
  snpst_dt="01NOV2018"d;
  run_ts=datetime();
  format run_ts datetime22.3 snpst_dt yymmdd10.;
  run_id=1;
run;

%let dt="&sysdate"d;
data run_table;
  modify run_table nobs=nobs point=nobs;
  if snpst_dt=&dt then
    run_id+1;
  else
    run_id=1;
  snpst_dt=&dt;
  run_ts=datetime();
  output;
  stop;
run;

You should just make sure to keep your run_table is sorted correctly, so that the newest OBS is last (if you sort it by anything other than run_ts, this will not work). This way, you do not have to append the data afterwards.

But if you want to occasionally add stuff from previous snapshot dates, the smart thing is to use an index, e.g.:

data run_table(index=(snpst_dt));
  snpst_dt="01NOV2018"d;
  run_ts=datetime();
  format run_ts datetime22.3 snpst_dt yymmdd10.;
  run_id=1;
run;

%let dt="&sysdate"d;
data run_table;
  snpst_dt=&dt;
  do run_id=1 by 1 until(_iorc_);
    modify run_table key=snpst_dt;
    end;
  _error_=0;
  run_ts=datetime();
  output;
  stop;
run;