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>
Subject: Re: days with gt 24 hours - merging by time
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
> 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
> Many thanks,
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).
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
Hope that helped you a bit, Patrick
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.) !! ':' !!
datetime2=input(compress(put(date2,date9.) !! ':' !!
proc print data=work.datasets;