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
... View more