Convert INTCK time character to numeric?

[jerrylshen]

I've searched up this a lot, but nothing worked for me. It's to my understanding that after I get the INTCK minute output, it's a character data type. So, I've tried converting the output by doing input(output, best.) and input(output, 5.) in the SAS data step as well as in proc sql but neither works.

Time is in the format of 24hr.

data want;
	length query_text $300 time_diff $20;
	set temp;
	
	query_text = "blah blah";
	time_diff = intck('minute', input(time1, time.), input(time2, time.)); 
	*time_diff = input(temp, 8.); //this doesn't work
	keep query_text Subject time_diff;
run;


proc sql;

CREATE TABLE temp2 AS
SELECT query_text, Subject, time_diff /* I've tried input(time_diff, best.) and input(time_diff, 5.) but neither worked */
FROM want
WHERE time_diff > 15 /*Error: Expression using IN has components that are of different data types */
;
 
quit;

Expected output:

╔════════════╦═════════╦═══════════╗
║ query_text             ║ Subject          ║        time_diff      ║
╠════════════╬═════════╬═══════════╣
║ blah                       ║ 001                ║           16             ║
║ blah                       ║ 002                ║           1500         ║
╚════════════╩═════════╩═══════════╝

It's kind of weird. If I have the WHERE statement be:

CREATE TABLE temp2 AS
SELECT query_text, Subject, time_diff  
FROM want
WHERE time_diff > 15 or time_diff < -15
;

It would print out everything, including when time_diff is 15 (same print output as when print "want") and no errors.

Any help/advice would be much appreciated, thanks!

Edit (after accepting solution): Thanks to some of the replies, I realized that I am indeed assigning the time/number to a character variable. So, I just initialized the "query_text" variable in the data step as

length query_text 300

Alright, thanks everyone!

[PGStats]

Do not overlook the log message

NOTE: Numeric values have been converted to character
      values at the places given by: (Line):(Column).
      13:17

it signals a problem with your code. INTCK returns numbers but you asked that it be assigned to a character variable. Hence the convertion mentionned in the log.

[Reeza]

It's to my understanding that after I get the INTCK minute output, it's a character data type.

That's incorrect, INTCK returns a number.

There are several points where you could have an error but I would start by double checking what the time and formats are for time1, time2 before you start. Once you can provide that, someone can provide the correct code.

Right now you're converting it to a number, so assuming it starts out as a character that looks like (08:24). If not, your INPUT is wrong.

Here's an example of how this would work if you had times.  

data have;
time_start = '5:45't;
time_end = '12:55't;
*calculate difference in minutes;
time_diff = intck('minutes', time_start, time_end);
*create another identical variable to show format;
time_formatted = time_diff;
format time_start time_end time_formatted time.;
run;

proc print data=have;
run;

If time were character variables, it may look like this:

data have;
    time_start='5:45';
    time_end='12:55';
    *calculate difference in minutes;
    time_diff=intck('minutes', input(time_start, time.), input(time_end, time.));
    *create another identical variable to show format;
    time_formatted=time_diff*60;
    format  time_formatted time.;
run;

Results:

Obs	time_start	time_end	time_diff	time_formatted
1	5:45:00	12:55:00	430	0:07:10

In the future including your log is also very helpful. It would contain any notes about types changing that would help indicate where the problem actually is. 

EDIT: edited to correct formatted time formats.

---

@jerrylshen wrote:

I've searched up this a lot, but nothing worked for me. It's to my understanding that after I get the INTCK minute output, it's a character data type. So, I've tried converting the output by doing input(output, best.) and input(output, 5.) in the SAS data step as well as in proc sql but neither works.

Time is in the format of 24hr.

data want;
	length query_text $300 time_diff $20;
	set temp;
	
	query_text = "blah blah";
	time_diff = intck('minute', input(time1, time.), input(time2, time.)); 
	*time_diff = input(temp, 8.); //this doesn't work
	keep query_text Subject time_diff;
run;


proc sql;

CREATE TABLE temp2 AS
SELECT query_text, Subject, time_diff /* I've tried input(time_diff, best.) and input(time_diff, 5.) but neither worked */
FROM want
WHERE time_diff > 15 /*Error: Expression using IN has components that are of different data types */
;
 
quit;

Expected output:

╔════════════╦═════════╦═══════════╗
║ query_text             ║ Subject          ║        time_diff      ║
╠════════════╬═════════╬═══════════╣
║ blah                       ║ 001                ║           16             ║
║ blah                       ║ 002                ║           1500         ║
╚════════════╩═════════╩═══════════╝

It's kind of weird. If I have the WHERE statement be:

CREATE TABLE temp2 AS
SELECT query_text, Subject, time_diff  
FROM want
WHERE time_diff > 15 or time_diff < -15
;

It would print out everything, including when time_diff is 15 (same print output as when print "want") and no errors.

Any help/advice would be much appreciated, thanks!

---

[jerrylshen]

Hi, I thought INTCK only takes in variables in the data type of
Date/Time/DateTime?

[Reeza]

The input variables required for INTCK are date time, time or date.

Datetime, time or date variables are just numeric values, with a format to show them as dates. Datetimes are the number of seconds from January 1, 1960, Date variables are the number of days from January 1, 1960 and Times are just seconds. 

Assuming you're referring to my second example, the INPUT() function within the INTCK() function converts the times to time variables. This is similar to what you were doing in your code. 

---

@jerrylshen wrote:
Hi, I thought INTCK only takes in variables in the data type of
Date/Time/DateTime?

---

[ChrisNZ]

@jerrylshen 

> I thought INTCK only takes in variables in the data type of Date/Time/DateTime?

You seem confused about SAS data types.

SAS only has 2 types: numeric (floating) and character (char).

Dates etc are simply numeric variables displayed using the appropriate format.

You can display any number with any format, and use any numeric function on any numeric data.

The use of the correct format and function is up to the user/programmer/DWH administrator.

[PGStats]

@Reeza, you shouldn't assign a time format to a time interval value unless it is expressed in seconds.

[Reeza]

---

@PGStats wrote:

@Reeza, you shouldn't assign a time format to a time interval value unless it is expressed in seconds.

---

Correct, hard to see that error since it just shifts the locations....the formats on the character variable are also incorrect.

07:10:00
00:07:10

[Tom · Accepted solution]

The output of INTCK() is a number.  The number of intervals crossed.  In your case since you are using MINUTE as the interval it is a count of the number of minutes.  

Why are you assigning that to a character variable?  When you do that SAS will format the number using the BEST12. format. So if the numbers of minutes was 15 then the character variable will have the ten spaces the digit 1 and then the digit 5.