get time in minutes

sam2929
sam2929 used Ask the Experts™
on
i want to get time in minutes

i have pdate 2011-04-21(ddmmyy10.) and ptime 2:38:23(time8.)
then   sdate 2011-04-20(ddmmyy10.) and stime 11:01:41(time8.)

i want to get time in minutes between pdatetime and sdatetime
if negative values the null it

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Marketing Consultant
Commented:
You have separate variables for the dates and the times so first you have to create date-time variables with the DMHS function.  After that you use the INTCK function to calculate the difference.

111  data test;

112     ** You already have these as sas date and time variables but I have to create them. ;
113     d1= '21May2011'd;
114     d2='20May2011'd;
115     t1='2:38:23't;
116     t2='11:01:41't;

117    ** Use the DHMS function to create date-time variables. ;
118    sasdt1=dhms(d1, hour(t1), minute(t1), second(t1));
119    sasdt2=dhms(d2, hour(t2), minute(t2), second(t2));
120
           ** Calculate the difference in minutes. ;
121     minutesDiff=intck('minute',sasdt1,sasdt2);
122
123     format sasdt1 sasdt2 datetime20.;
124
125     put _all_;
126  run;

d1=18768 d2=18767 t1=9503 t2=39701 sasdt1=21MAY2011:02:38:23 sasdt2=20MAY2011:11:01:41 minutesDiff=-937 _ERROR_=0 _N_=1
NOTE: The data set WORK.TEST has 1 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Author

Commented:
do i have to do this in custom transformer or can i use in expression
my data is in table i am using 4.2.1 i mean what kind of transfomer is need to it ?
d507201Database Marketing Consultant

Commented:
You're using DI Studio?  I'm not familiar enough with it to say how to do it, but all you're doing is creating a new variable.  You already have the date and time variables d1, d2, t1, and t2 so just create a new variable using this formula..

minutesDiff=intck('minute',
      dhms(d1, hour(t1), minute(t1), second(t1)),
      dhms(d2, hour(t2), minute(t2), second(t2)));
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
ok can we do minutesdiff in sql tht will be easy
there's a function similar to minutesdiff in sql which is shown in  d507201's 1st post;

  ** Calculate the difference in minutes. ;
121     minutesDiff=intck('minute',sasdt1,sasdt2);

the only thing is that because your date and time are in different fields, some processing needs to be done to put the 2 values into 1 field before you can use the function. I'm sure this is the similar case in sql.

Author

Commented:
its not liking intck function

W5ZXHLPB.em_proc_tme_zone length = 8,
      W5ZXHLPB.eventdatetime length = 8,
      W5ZXHLPB.processdatetime length = 60,
      W5ZXHLPB.transmitdatetime length = 8,
      INTCK('MINUTE',W5ZXHLPB.processdatetime ,W5ZXHLPB.eventdatetime )
       as evt_to_em_proc_tme_lag length = 8

put "WARNING%QUOTE(:) Mapped source columns do not match columns used in"
        " expression

but columns are there
d507201Database Marketing Consultant

Commented:
W5ZXHLPB.processdatetime length = 60,
 
This tells me that the varible is character.  It needs to be numeric, length=8 like eventDateTime.

Author

Commented:
when i do
dhms for 2011-04-25 20:56:38
ouput comes

1619384198
what this number means
apply format datetime. (datetime with a period at the end of it) to the format column of the corresponding record.

this is the time in seconds elapsed since 01 Jan 1960 midnight.

also, using DI studio, it will prompt you when columns are not mapped properly. just drag the 2 columns you need into the target column. however, if you don't do so it won't harm the code flow...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial