Data

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 8 bytes and strings can be much larger.

Best Practice

Use integers for matching, not strings.


The following code shows that the string ""QuestionDesc" was up to 2500 byes, and matching on 2500 bytes will be slower than matching on 8 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 sped up the process considerably. 


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