"Group By" clause keep giving me an error, not sure why.

[ralizadeh]

I have created the code below in SAS EG. For some reasons I can't pass the group by clause. 

LIBNAME TDPROD    	TERADATA SERVER = KNPROD
	SCHEMA = KN1PRODVIEWSL5
	AUTHDOMAIN = TERAAUTH;

PROC SQL;
	CREATE TABLE SASUSER.DE_2 AS (
		SELECT
			Count(Distinct(CLAIMS_HDR.AKA_CIN)),
			CLAIMS_HDR.SVC_FROM_DT_MM,
		CASE
			WHEN CLAIMS_HDR.Age < 18 THEN 'Child'
			WHEN CLAIMS_HDR.Age BETWEEN 18 AND 64 THEN 'Adult'
			WHEN CLAIMS_HDR.Age > 64 THEN '65+'
			ELSE NULL
		END 
	AS Age_Group
		FROM
			TDPROD.CLAIMS_DTL INNER JOIN TDPROD.CLAIMS_HDR ON (CLAIMS_DTL.RECORD_ID = CLAIMS_HDR.RECORD_ID)
		INNER JOIN TDPROD.ELIGIBILITY ON (ELIGIBILITY.ELIG_DT_MM    =  CLAIMS_HDR.SVC_FROM_DT_MM    AND 
			ELIGIBILITY.ELIG_DT_YYYY  = CLAIMS_HDR.SVC_FROM_DT_YYYY  AND  ELIGIBILITY.AKA_CIN=CLAIMS_HDR.AKA_CIN)
		INNER JOIN TDPROD.CIN_XREF  ClaimsHdr ON (ClaimsHdr.AKA_CIN=CLAIMS_HDR.AKA_CIN)
			LEFT OUTER JOIN TDPROD.CLAIMS_HDR_DIAG_ICD10 ON (CLAIMS_HDR_DIAG_ICD10.RECORD_ID=CLAIMS_HDR.RECORD_ID)
			LEFT OUTER JOIN TDPROD.CLAIMS_HDR_SURG_ICD10 ON (CLAIMS_HDR_SURG_ICD10.RECORD_ID=CLAIMS_HDR.RECORD_ID)
				WHERE
					(
					( ELIGIBILITY.MC_STAT_A NOT IN (' ','0','9') OR ELIGIBILITY.MC_STAT_B NOT IN (' ','0','9') OR ELIGIBILITY.MC_STAT_D NOT IN (' ','0','9')
					)
					AND
					(
					CLAIMS_HDR.INPAT_DISCHARGE_DT  BETWEEN '01OCT2022'd AND '31DEC2022'd
					OR
					(
					CLAIMS_HDR.SVC_TO_DT  BETWEEN '01OCT2022'd AND '31DEC2022'd
					AND
					CLAIMS_HDR.INPAT_DISCHARGE_DT  Is Null  
						)
						)
						AND
						(
						CLAIMS_DTL.PROC_CD  IN  ( 'H0008','H0009','H0010','H0011','H0012','H0013','H0014','HZ2ZZZZ'  )
						OR
						CLAIMS_HDR_SURG_ICD10.SURG_CD_ICD10  IN  ( 'HZ2ZZZZ'  )
						OR
						CLAIMS_DTL.REVENUE_CD  IN  ( '0116','0126','0136','0146','0156'  )
							)
							AND
							CLAIMS_HDR_DIAG_ICD10.DIAG_CD_ICD10  IN  ( 'F10120','F10121','F10129','F10130','F10131','F10132','F10139','F1014','F10150','F10151','F10159','F10180','F10181','F10182','F10188','F1019','F1020','F10220','F10221','F10229','F10230','F10231','F10232','F10239','F1024','F10250','F10251','F10259','F1026','F1027','F10280','F10281','F10282','F10288','F1029','F11121','F11122','F11129','F1113','F1114','F11150','F11151','F11159','F11181','F11182','F11188','F1119','F1120','F11220','F11221','F11222','F11229','F1123','F1124','F11250','F11251','F11259','F11281','F11282','F11288','F1129','F1210','F12120','F12121','F12122','F12129','F1213','F12150','F12151','F12159','F12180','F12188','F1219','F1220','F12220','F12221','F12222','F12229','F1223','F12250','F12251','F12259','F12280','F12288','F1229','F1310','F13120','F13121','F13129','F13130','F13131','F13132','F13139','F1314','F13150','F13151','F13159','F13180','F13181','F13182','F13188','F1319','F1320','F13220','F13221','F13229','F13230','F13231','F13232','F13239','F1324','F13250','F13251','F13259','F1326','F1327','F13280','F13281','F13282','F13288','F1329','F1410','F14120','F14121','F14122','F14129','F1413','F1414','F14150','F14151','F14159','F14180','F14181','F14182','F14188','F1419','F1420','F14220','F14221','F14222','F14229','F1423','F1424','F14250','F14251','F14259','F14280','F14281','F14282','F14288','F1429','F1510','F15120','F15121','F15122','F15129','F1513','F1514','F15150','F15151','F15159','F15180','F15181','F15182','F15188','F1519','F1520','F15220','F15221','F15222','F15229','F1523','F1524','F15250','F15251','F15259','F15280','F15281','F15282','F15288','F1529','F1610','F16120','F16121','F16122','F16129','F1614','F16150','F16151','F16159','F16180','F16183','F16188','F1619','F1620','F16220','F16221','F16229','F1624','F16250','F16251','F16259','F16280','F16283','F16288','F1629','F1810','F18120','F18121','F18129','F1814','F18150','F18151','F18159','F1817','F18180','F18188','F1819','F1820','F18220','F18221','F18229','F1824','F18250','F18251','F18259','F1827','F18280','F18288','F1829','F1910','F19120','F19121','F19122','F19129','F19130','F19131','F19132','F19139','F1914','F19150','F19151','F19159','F1916','F1917','F19180','F19181','F19182','F19188','F1919','F1920','F19220','F19221','F19222','F19229','F19230','F19231','F19232','F19239','F1924','F19250','F19251','F19259','F1926','F1927','F19280','F19281','F19282','F19288','F1929'  )
							)
						GROUP BY
							CLAIMS_HDR.SVC_FROM_DT_MM,
							Age_Group);
QUIT;

LIBNAME TDPROD CLEAR;

Here is the error I get:

72         						GROUP BY
73         							CLAIMS_HDR.SVC_FROM_DT_MM,
74         							Age_Group);
ERROR: The following columns were not found in the contributing tables: NULL.
NOTE: The following 1 column(s) in TDPROD.ELIGIBILITY  have been automatically dropped because they have a datatype that is not 
      supported by this engine:
ELIGIBILITY_SHAPE 
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
75         QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.29 seconds
      cpu time            0.17 seconds

[Tom · Accepted solution]

The error seems to be saying you are trying to reference a variable named NULL.

Probably from the this CASE clause?  Which also seems to have some HTML tags in there, but perhaps that was introduced by you copying and pasting the text from a webpage?

, CASE
    WHEN CLAIMS_HDR.Age < 18 THEN 'Child'
    WHEN CLAIMS_HDR.Age BETWEEN 18 AND 64 THEN 'Adult'
    WHEN CLAIMS_HDR.Age > 64 THEN '65+'
    ELSE NULL
  END AS Age_Group

If you want AGE_GROUP to be blank when none of the conditions are met then use

ELSE ' ' 

But also your ELSE condition can never happen.  Values from 18 thru 64 will be set to 'Adult' and values larger than 64 will be set to '65+' and everything else will be set to 'Child'.  SAS only does BINARY logic.  Missing numeric values are considered less than any actual number.

Something like this will catch the missing values and also any negative values.

, CASE
    WHEN CLAIMS_HDR.Age BETWEEN 0 AND 18 THEN 'Child'
    WHEN CLAIMS_HDR.Age BETWEEN 18 AND 64 THEN 'Adult'
    WHEN CLAIMS_HDR.Age > 64 THEN '65+'
    ELSE ' '
  END AS Age_Group

[ralizadeh]

Thank you, Tom. It solved the problem.