Date:         Tue, 11 Sep 2007 04:08:08 -0700
Reply-To:     Patrick <patrick.matter@GMX.CH>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Patrick <patrick.matter@GMX.CH>
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"

On Sep 11, 12:54 pm, "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

Hi Theorbo

I hope that the code example below solves part of your problem.
If anyone in this community has a better idea how to combine a SAS
date with a SAS time and combine it into a SAS datetime: I'm very open
for suggestions (I know: I could do it different while reading the raw
data; but let's assume the variables date and time are already part of
a sas dataset).

Theorbo:
For your understanding (you might already know that): The variables
datetime1 & datetime2 are numeric. Their value contains the seconds
from 01jan1960 until the date and time I've read from your data.

I didn't fully understand how you want to merge the data and couldn't
therefore send an example. I also didn't understand why case 1:
09/01/07, 1:00 AM is the same like case 2: 09/01/07, 25:01:00. For my
understanding only case 2 corresponds to 09/02/07, 1:00 AM - but you
will know your data better than I do. The fact is: SAS treats your
data the way I understand it what means: If you want that case 1 is
equal to case 2 for merging, you will have to add the seconds of one
day to the variable datetime1 of case1 (=datetime1 + (24*60*60)).

There are a lot of useful SAS functions, formats and informats for
dealing with dates. It's really worth reading the "SAS Language
Reference: Dictionary".

Hope that helped you a bit, Patrick

data work.datasets;
        attrib
                date1 date2 informat=yymmdd10. format=date9.
                time1 time2 informat=time10.   format=time8.
                datetime1   informat=datetime18. format=datetime18.
                datetime2   informat=datetime18. format=datetime18.
        ;
  infile datalines delimiter=',';
        input  date1 time1 date2 time2;
        datetime1=input(compress(put(date1,date9.) !! ':' !!
put(time1,time8.)),datetime18.);
        datetime2=input(compress(put(date2,date9.) !! ':' !!
put(time2,time8.)),datetime18.);

        datalines;
09/01/07,1:00AM,09/01/07,25:00:30
09/01/07,1:01AM,09/01/07,25:01:00
09/01/07,1:02AM,09/01/07,25:02:00
09/01/07,1:02AM,09/01/07,25:02:30
09/01/07,1:03AM,09/01/07,25:03:00
;
run;

proc print data=work.datasets;
run;