BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NareshAbburi
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

6 REPLIES 6
DBailey
Lapis Lazuli | Level 10

You would use

SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME)

NareshAbburi
Calcite | Level 5

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.

NareshAbburi
Calcite | Level 5

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

PGStats
Opal | Level 21

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

PG
NareshAbburi
Calcite | Level 5

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);

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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