Date:         Thu, 21 Jun 2007 15:46:08 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Append file every day

On Sun, 17 Jun 2007 11:33:08 -0700, Wade Pan <wade_pan@YAHOO.COM> wrote:

>Hi, guys,
>I have data Have (say it has two variables A and B) which updates every
day. I need to append Have everyday. After appending, I need to query data
from different day. So I create another variable Currentdate. My code is
below. I have two questions:
>1. is my code correct?
>2. one thing bothering me is that if I run the code twice on a day, it will
append the data twice, which is not what I want.
>Do you guys have better idea?
>Thanks!
>
>/*---------------------------------------------*/
>data have;
>input a b;
>datalines;
>1 2;
>
>proc sql;
> create table t1 as
>  select a, b, today() as currentdate
>  from have;
>
>create table t2 as
>select *
>from t1;
>
>drop table t1;
>quit;
>
>proc append base=need data=t2;
>run;

One source of error is running the code multiple times on the same day.

Another possible problem is failure (or poor synchronization) of the process
which replaces HAVE, so that the operation of your appending code on
consecutive days actually picks up the same data twice (and perhaps misses
an iteration of HAVE occurring late on the second day).

I would want some kind of signature for each successive version of HAVE, and
I would embed that either in NEED or in a companion table tracking the
history (one observation per day).

If you cannot control or influence the process which delivers HAVE and
arrange such a signature, perhaps you can construct one ex post using such
things as the host OS timestamp, a record count, and some sort of checksum.