Desktop productivity for business analysts and programmers

Time Variable Difference - hard time

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Time Variable Difference - hard time

Hi,

I'm trying to find two time variable difference using below query. but it is throwing me the following error. Can someone let me know what mistake I did.

Please help me.

QUERY:

PROC SQL;

CREATE TABLE WIS.TEMP7 AS

SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

Result:

5         PROC SQL;

16         CREATE TABLE WIS.TEMP7 AS

17         SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

ERROR: Invalid date/time/datetime constant 'MAX_TIME'T.

ERROR: Invalid date/time/datetime constant 'MIN_TIME'T.

18         SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM

18       ! MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

but the table is getting created using second query.

time.PNG


Accepted Solutions
Solution
‎11-04-2014 02:34 PM
Respected Advisor
Posts: 4,826

Re: Time Variable Difference - hard time

Column TIME must be CHARACTER, not NUMERIC. Try converting it with

SELECT MAX(INPUT(TIME, TIME8.)) AS MAX_TIME, MIN(INPUT(TIME, TIME8.)) AS MIN_TIME


then, if you want a positive number of minutes, you should use


INTCK('MINUTE', MIN_TIME, MAX_TIME)


PG

PG

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: Time Variable Difference - hard time

You would use

SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME)

Contributor
Posts: 40

Re: Time Variable Difference - hard time

Then I'm getting this error;

ERROR: Function INTCK requires a numeric expression as argument 2.

ERROR: Function INTCK requires a numeric expression as argument 3.

not sure, if i need to convert it.

Contributor
Posts: 40

Re: Time Variable Difference - hard time

could someone help with this issue.....?

Respected Advisor
Posts: 4,826

Re: Time Variable Difference - hard time

Post the log, including new code and error(s). - PG

PG
Contributor
Posts: 40

Re: Time Variable Difference - hard time

below are the two queries that I've tried:

PROC SQL;

CREATE TABLE WIS.TEMP7 AS

SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME) FROM (

SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

14         GOPTIONS ACCESSIBLE;

15         PROC SQL;

16         CREATE TABLE WIS.TEMP7 AS

17         SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME) FROM (

18         SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM

18       ! MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

ERROR: Function INTCK requires a numeric expression as argument 2.

ERROR: Function INTCK requires a numeric expression as argument 3.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

19        

20         GOPTIONS NOACCESSIBLE;

21         %LET _CLIENTTASKLABEL=;

22         %LET _CLIENTPROJECTPATH=;

23         %LET _CLIENTPROJECTNAME=;

24         %LET _SASPROGRAMFILE=;

25        

26         ;*';*";*/;quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

     

26       !                run;

27         ODS _ALL_ CLOSE;

QUERY:

PROC SQL;

CREATE TABLE WIS.TEMP7 AS

SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

Result:

5         PROC SQL;

16         CREATE TABLE WIS.TEMP7 AS

17         SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

ERROR: Invalid date/time/datetime constant 'MAX_TIME'T.

ERROR: Invalid date/time/datetime constant 'MIN_TIME'T.

18         SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM

18       ! MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

Solution
‎11-04-2014 02:34 PM
Respected Advisor
Posts: 4,826

Re: Time Variable Difference - hard time

Column TIME must be CHARACTER, not NUMERIC. Try converting it with

SELECT MAX(INPUT(TIME, TIME8.)) AS MAX_TIME, MIN(INPUT(TIME, TIME8.)) AS MIN_TIME


then, if you want a positive number of minutes, you should use


INTCK('MINUTE', MIN_TIME, MAX_TIME)


PG

PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 868 views
  • 0 likes
  • 3 in conversation