Page tree



Back to Knowledge Base

Use Integers for Matching

Purpose

The following is a guide on using integers for matching in SAS Viya, versus using strings. Integers are eight bytes and strings can be much larger.

Best Practice

Use integers for matching, not strings since it will result in a faster run time.


The following code shows that the string ""QuestionDesc" was up to 2,500 byes, and matching on 2,500 bytes will be slower than matching on eight bytes.

PROC SQL;
	CREATE TABLE mytable AS
	SELECT DISTINCT a.BeneID,
		SUM(CASE WHEN TestID=‘ABC-123’
			AND QuestionDesc=‘This question description...’
			THEN 1
			ELSE 0
		END) AS NumTests
	FROM table_xyz a
	GROUP BY a.BeneID
	ORDER BY a.BeneID;
QUIT;

"AND QuestionDesc=‘This question description...’" will take more than 15 hours to run. Using an integer as a lookup value will speed up the process. 


PROC SQL;
	CREATE TABLE mytable AS
	SELECT DISTINCT a.BeneID,
		SUM(CASE WHEN TestID=‘ABC-123’
			AND QuestionID=456
			THEN 1
			ELSE 0
		END) AS NumTests
	FROM table_xyz a
	GROUP BY a.BeneID
	ORDER BY a.BeneID;
QUIT;

"AND QuestionID=456" will run in less than 10 minutes (50-100 times faster than strings).