Date:         Tue, 11 Sep 2007 09:54:52 -0700
Reply-To:     "ajs2004@bigfoot.com" <ajs2004@BIGFOOT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "ajs2004@bigfoot.com" <ajs2004@BIGFOOT.COM>
Organization: http://groups.google.com
Subject:      Re: days with gt 24 hours - merging by time
Comments: To: sas-l@uga.edu
In-Reply-To:  <QrnFi.1114$Af1.3@trnddc06>
Content-Type: text/plain; charset="us-ascii"

As Howard says, just convert to using a proper datetime field.

Presumably, the data sets actually contain more information than
you've shown? (Otherwise, they are just two representations of the
same data, so merging them achieves nothing).

You have an additional problem that the time precision is different
between the two (one has seconds, and one doesn't), so exact matching
based on time (or datetime) is impossible. However, if the records
match up one-to-one, as they do in the sample you've shown, you could
do a merge without a BY statement.

On Sep 11, 3:54 am, "Theorbo" <re...@to-group.nfo> wrote:
> Hi. I'm looking for some other ways of thinking about my seemingly-simple
> problem. I have 2 datasets of radio ad-sales data and I want to merge them
> together, primarily by TIME. The data's definition of 'day' is approximately
> 6am on one day through 6am on the following day. I would like to keep the
> definition that time after midnight until 6 am is still the previous day.
>
> __Dataset 1__
> obs, date, time, program, advertiser
> 1,    09/01/07,     1:00 AM,    The News,    Advertiser A
> 2,    09/01/07,     1:01 AM,    The News,    Advertiser B
> 3,    09/01/07,     1:02 AM,    The News,    Advertiser C
> 4,    09/01/07,     1:02 AM,    The News,    Advertiser A
> 5,    09/01/07,     1:03 AM,    The News,    Advertiser D
>
> __Dataset 2__
> obs, date, time, program, advertiser
> 1,    09/01/07,     25:00:30,    The News,    Advertiser A
> 2,    09/01/07,     25:01:00,    The News,    Advertiser B
> 3,    09/01/07,     25:02:00,    The News,    Advertiser C
> 4,    09/01/07,     25:02:30,    The News,    Advertiser A
> 5,    09/01/07,     25:03:00,    The News,    Advertiser D
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> In both of these datasets, the ACTUAL DAY (by the calendar) is 9/2, but I
> need to consider it 9/1 for my purposes.  Ideally, it seems that the best
> way would be to keep the dates as indicated in the datasets but to have time
> (hour values) go to 29-30 each 'day'.
>
> I've tried a few things and everything so far seems like a hack-job or
> worse.    I've tried:
>
>     1) scrapping SAS date & time values altogether and just using hour & day
> variables (1-xy) - not good for the long run because you always need a
> codebook to know what an hour and day variable value correspond with. I want
> to use SAS time/date values if at all
> possible.
>
>    2) using informats - but I couldn't find an informat that easily dealt
> with the time in Dataset 1 (AM/PM) or Dataset 2 with hours gt 23
>
>    3) reading the dates and times as $ and then substring-ing out what I
> need to piece it all back together. Doesn't seem like the HMS() will handle
> hours gt 23
>
> Saying all of this, I imagine that I'm fixating on one solution for skinning
> this cat. What are some other, perhaps-obvious, ways to look at this
> problem?
>
> Many thanks,
> theorbo