Hi All,

I came across this issue when I tried using DHMS function in PROC SQL.
Below is code used

proc sql;
CREATE TABLE temp1 AS
	SELECT A.*, DHMS(TEMPDT, HOUR(TEMPTM), MIN(TEMPTM), SECOND(TEMPTM))
AS                 TEMPDM FORMAT =  DATETIME20.
	FROM TEMP A
	ORDER BY SUBJID, TEMPDT, TEMPTM;
QUIT;

     SUBJID       tempdt      temptm                  tempdm

               1    03MAR2009    15:13       03MAR2009:16:00:00
               3    07APR2009    19:05       07APR2009:20:00:00
               4    11MAR2009     7:00       11MAR2009:08:00:00
               7    25AUG2009     8:53       25AUG2009:09:00:00


If you look carefully at the above syntax then instead of using
"MINUTE" in DHMS, I've used "MIN" by mistake and that caused this
problem. However if I were to use this function in data step and type
in 'MIN' instead of 'MINUTE' the data step wouldn't run & instead give
syntax error. But, PROC SQL doesn't seem to be parsing this right & do
not issue any syntax error and give misleading result.

Other problem is if you pass this each record separately in PROC SQL &
then use DHMS to read the date & time value it will give some weird
date/time. For example if I only pass '03mar2009' & '15:13' for subjid
= 1 to PROC SQL & use the DHMS function on it using 'MIN' to read the
minutes instead of 'MINUTE' then the resulting date/time is
10APR2009:16:00:00.

Can someone please tell me as to what could be causing this problem
and has anyone come across such a problem in the past?

Thanks in advance!

I suspect that SQL is happily calculating the minimum values for
TEMPTM over the whole dataset and using it for each row as MIN() is a
aggreate function that SQL can calculate (see also MAX() and COUNT()).

To avoid the risk of making this error again change:
   dhms(tempdt,hour(temptm),minute(temptm),second(temptm))
to
  dhms(tempdt,0,0,temptm)

It will be easier to type and for SAS to compute and harder to
misstype into a valid function call.


On Apr 27, 3:06=A0pm, HARY <hardik...@gmail.com> wrote:
> Hi All,
>
> I came across this issue when I tried using DHMS function in PROC SQL.
> Below is code used
>
> proc sql;
> CREATE TABLE temp1 AS
> =A0 =A0 =A0 =A0 SELECT A.*, DHMS(TEMPDT, HOUR(TEMPTM), MIN(TEMPTM), SECON=
D(TEMPTM))
> AS =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 TEMPDM FORMAT =3D =A0DATETIME20.
> =A0 =A0 =A0 =A0 FROM TEMP A
> =A0 =A0 =A0 =A0 ORDER BY SUBJID, TEMPDT, TEMPTM;
> QUIT;
>
> =A0 =A0 =A0SUBJID =A0 =A0 =A0 tempdt =A0 =A0 =A0temptm =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0tempdm
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A003MAR2009 =A0 =A015:13 =A0 =A0 =
=A0 03MAR2009:16:00:00
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A007APR2009 =A0 =A019:05 =A0 =A0 =
=A0 07APR2009:20:00:00
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A04 =A0 =A011MAR2009 =A0 =A0 7:00 =A0 =A0 =
=A0 11MAR2009:08:00:00
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A07 =A0 =A025AUG2009 =A0 =A0 8:53 =A0 =A0 =
=A0 25AUG2009:09:00:00
>
> If you look carefully at the above syntax then instead of using
> "MINUTE" in DHMS, I've used "MIN" by mistake and that caused this
> problem. However if I were to use this function in data step and type
> in 'MIN' instead of 'MINUTE' the data step wouldn't run & instead give
> syntax error. But, PROC SQL doesn't seem to be parsing this right & do
> not issue any syntax error and give misleading result.
>
> Other problem is if you pass this each record separately in PROC SQL &
> then use DHMS to read the date & time value it will give some weird
> date/time. For example if I only pass '03mar2009' & '15:13' for subjid
> =3D 1 to PROC SQL & use the DHMS function on it using 'MIN' to read the
> minutes instead of 'MINUTE' then the resulting date/time is
> 10APR2009:16:00:00.
>
> Can someone please tell me as to what could be causing this problem
> and has anyone come across such a problem in the past?
>
> Thanks in advance!