topic Re: Trying to get rid of error message regarding numeric datatype required to use division error. in New SAS User

Trying to get rid of error message regarding numeric datatype required to use division error.

CSB35 — Thu, 28 Mar 2024 17:02:52 GMT

The data that I am using to divide says it is all numeric when I look at the table. When I try to divide two numeric values I get the error message 'ERROR: Expression using division (/) requires numeric types.' The first two create table blocks of code work and create a table with numeric data types. The third block gives me the error. I have copied the error log at the very bottom. Please let me know if you know how to solve this issue. I am working on SAS Citrix Workspace, not the SAS app in case that is relevant.

/*Create table to compare PY month actual expenses to current year*/
PROC SQL;
   CREATE TABLE WORK.PYActualsToCYActuals AS 
   SELECT t1.'GL account'n AS 'GL Account'n, 
   (MEAN(t1.Jan)) AS 'Month PY Actuals'n, 
   (SUM(t2.'Monthly Actuals'n)) As 'CY Actuals'n,

		((MEAN(t1.Jan)) - (SUM(t2.'Monthly Actuals'n))) AS Variance,
		((((MEAN(t1.Jan)) - (SUM(t2.'Monthly Actuals'n))))) AS 'Variance %'n

   FROM WORK.CLEAN_PY_YTD t1
   	FULL JOIN Work.Monthly_Actuals t2 ON (t1.'GL Account'n = t2.'G/L Account'n)
	WHERE (t1.Jan NE . AND t1.Jan NE 0 AND t1.'GL account'n NE 'Grand Total') 
	OR (t2.'Monthly Actuals'n NE . AND t2. 
	'Monthly Actuals'n NE 0 AND t1.'GL account'n NE 'Grand Total')

	GROUP BY t1.'GL Account'n;

Quit;

/*Change all . to 0s in the table*/
PROC SQL;
CREATE TABLE WORK.PYActualsToCYActuals AS
	SELECT 'GL Account'n,
		CASE WHEN ('Month PY Actuals'n = .) THEN ('Month PY Actuals'n = 0) 
		ELSE 'Month PY Actuals'n END AS 'Month PY Actuals'n,
		CASE WHEN ('CY Actuals'n = .) THEN ('CY Actuals'n = 0) 
		ELSE 'CY Actuals'n END AS 'CY Actuals'n,
		CASE WHEN ('CY Actuals'n = .) THEN ('CY Actuals'n = 0) 
		ELSE 'CY Actuals'n END AS 'CY Actuals'n,
		CASE WHEN ('Variance'n = .) THEN ('Variance'n = 0) 
		ELSE 'Variance'n END AS 'Variance'n,
		CASE WHEN ('Variance %'n = .) THEN ('Variance %'n = 0) 
		ELSE 'Variance %'n END AS 'Variance %'n
	FROM WORK.PYActualsToCYActuals;

QUIT;

/*Solve for variance as a percent*/
PROC SQL;
	CREATE TABLE WORK.PYActualsToCYActuals AS
	SELECT 'GL Account'n, 'Month PY Actuals'n, 'CY Actuals'n, 'Variance'n,
		CASE WHEN ('Month PY Actuals'n NE 0) AND ('CY Actuals'n NE 0)
			THEN ('Variance'n / 'Month PY Actuals'n) * 100
		WHEN 'CY Actuals'n NE 0
			THEN ('Variance'n / 'CY Actuals') * 100
		WHEN 'Month PY Actuals'n NE 0
			THEN ('Variance'n / 'Month PY Actuals'n) * 100
		ELSE 100 END AS 'Variance %'n

	FROM WORK.PYActualsToCYActuals;

Quit;


144        
145        /*Solve for variance as a percent*/
146        PROC SQL;
147        	CREATE TABLE WORK.PYActualsToCYActuals AS
148        	SELECT 'GL Account'n, 'Month PY Actuals'n, 'CY Actuals'n, 'Variance'n,
149        		CASE WHEN ('Month PY Actuals'n NE 0) AND ('CY Actuals'n NE 0)
150        			THEN ('Variance'n / 'Month PY Actuals'n) * 100
151        		WHEN 'CY Actuals'n NE 0
152        			THEN ('Variance'n / 'CY Actuals') * 100
153        		WHEN 'Month PY Actuals'n NE 0
154        			THEN ('Variance'n / 'Month PY Actuals'n) * 100
155        		ELSE 100 END AS 'Variance %'n
156        
157        	FROM WORK.PYActualsToCYActuals;
ERROR: Expression using division (/) requires numeric types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
158        
159        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

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

ballardw — Thu, 28 Mar 2024 17:27:47 GMT

What does "look at the table" mean? Print and screen views do not normally tell you the variable type.

You need to look at the variable Properties, either Proc contents to get an output summary, click on the column heading in table viewer or if you use the SAS Explorer you can right click on a data set and select the View Columns to see the variable name, types labels and formats. Or use the Proc SQL describe table syntax.

I tend to believe SAS when it reports something as not numeric. Either a variable is not numeric or something has snuck into your code that makes the expression get treated as not numeric by SAS.

Your Case statements are extremely lucky that you wanted to assign the value of 0 because in

	CASE WHEN ('CY Actuals'n = .) THEN ('CY Actuals'n = 0) 
		ELSE 'CY Actuals'n END AS 'CY Actuals'n,

The "then ('CY Actuals'n = 0)" bit of code is assigning the LOGICAL value of the comparison, which happens to be (. = 0) which is false and returns a number 0.

better (and clearer for folks reading your code)

	CASE WHEN ('CY Actuals'n = .) THEN 0 
		ELSE 'CY Actuals'n END AS 'CY Actuals'n,

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

PaigeMiller — Thu, 28 Mar 2024 17:39:15 GMT

My personal rule is that when SAS says something is not numeric, then it is not numeric, regardless of what the programmer says.

Check the PROC CONTENTS output for data set work.pyactualstocyactuals

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

FreelanceReinh — Thu, 28 Mar 2024 17:54:58 GMT

@CSB35 wrote:

151        		WHEN 'CY Actuals'n NE 0
152        			THEN ('Variance'n / 'CY Actuals') * 100

The forgotten "n" turns the name literal of numeric variable 'CY Actuals'n into a character constant 'CY Actuals'.

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

Astounding — Thu, 28 Mar 2024 17:56:35 GMT

You are missing the "n" following one of the references to

'CY_Actuals'

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

Tom — Thu, 28 Mar 2024 18:20:22 GMT

Why did you create variable names with spaces and other strange characters so that you are forced to use name literals to reference them.

Try changing the value of VALIDVARNAME option to V7 so that SAS will give you errors when you try to do that.

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

Ksharp — Fri, 29 Mar 2024 02:12:55 GMT

You should use DIVIDE() function instead of '/' operator .

Like:

change

'Variance'n / 'Month PY Actuals'n

into

 divide(  'Variance'n , 'Month PY Actuals'n )

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

CSB35 — Fri, 29 Mar 2024 18:14:45 GMT

Thank you for the valuable information, especially on the Case statements! I did not know that and this will greatly improve my coding going forward.

Regarding looking at the table, the create table blocks write over each other so that the end result is just one table (not sure if that is bad to do). If I only ran the code for the first two blocks it created a table showing the datatype as numeric, which was true, but I was missing an 'n' by one of my variables caused it to be read as a string/creating the error.

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

CSB35 — Fri, 29 Mar 2024 18:17:17 GMT

That is a good point. I am going to save that line for later haha. I was missing an n on one of my column header names causing it to be read as a string.

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

CSB35 — Fri, 29 Mar 2024 18:18:55 GMT

Thank you!

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

CSB35 — Fri, 29 Mar 2024 18:19:46 GMT

Will try this! Thank you.

Re: Trying to get rid of error message regarding numeric datatype required to use division error.

CSB35 — Fri, 29 Mar 2024 18:20:32 GMT

Thank you! Will use this going forward.