Prev: HTML Web Developer -- IL - 3 Months
Next: zipcode distance caculation
From: HARY on 27 Apr 2010 15:06
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!
From: Tom Abernathy on 27 Apr 2010 17:28
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 pm, HARY <hardik...(a)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
>         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!

 | 
Pages: 1
Prev: HTML Web Developer -- IL - 3 Months
Next: zipcode distance caculation