Hi folks,
I have this SUM function that is giving me the error message:
The block of PROC SQL code that is giving me trouble looks like this:
SUM(CASE
WHEN 'AoE'n = "BAG"
THEN (GN.Principal * CALCULATED 'DC Term'n)
ELSE (GN.Principal * GN.'FAC Term'n)
END) AS 'FTP LP Y'n
When I execute the code without the SUM(), it works and shows all values in the column (calculated from other values). What I need to do is SUM all the values in that column into a singular cell....What is odd to me is that when I used SUM() here, it does work, which I am using in an earlier portion of my PROC SQL code and it looks like this:
SUM(CASE
WHEN 'FAC Type'n = "DC"
THEN (GN.Principal * GN.'FAC Term'n)
ELSE 0
END)
/
SUM(CASE
WHEN 'FAC Type'n = "DC"
THEN GN.Principal
ELSE 0
END) AS 'DC Term'n,
I can't seem to figure out the differences in syntax between the two blocks of code. Does anyone have any ideas why I might be getting this error?
The problem must be with CALCULATED 'DC Term'n. DC Term must already be a sum. So you basically have SUM(SUM(something)). Show us more for a possible workaround.
Hey you are totally right, which I eventually realized, but still don't know how to get around this since the only way I can think of getting 'DC Term'n is via the sum, which leaves me with a nested sum which PROC SQL seems to dislike. This is the code from 'DC Term'n:
SUM(CASE WHEN 'FAC Type'n = "DC"
THEN GN.Principal
ELSE 0
END) AS 'DC Term'n
So currently it seems that what I am basically asking PROC SQL to do is:
SUM(CASE WHEN 'AoE'n = "BAG"
THEN GN.Principal * SUM(CASE WHEN 'FAC Type'n = "DC" THEN GN.Principal ELSE 0 END)
ELSE GN.Principal * GN.'FAC Term'n
END) AS 'FTP LP Y'n
and clearly PROC SQL/SAS does not like that. How would I get around this?
I don't see any way around this. You will need two queries or, if you want to avoid creating an intermediate table, two nested queries to do both sums.
Please explain what you are trying to do. Please explain what your grouping variable(s) is. Post a small sample set of data (in the form of a data step that we can run to re-create it) and what result you are trying to get.
Also is there any reason you need to use SQL instead of just writing normal SAS code?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: