topic Re: SUM function doesn't work on a basic CASE statement output? in SAS Enterprise Guide

SUM function doesn't work on a basic CASE statement output?

atran2 — Wed, 18 Dec 2019 20:44:39 GMT

Hi folks,

I have this SUM function that is giving me the error message:

ERROR: Summary functions nested in this way are not supported.

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?

Re: SUM function doesn't work on a basic CASE statement output?

PGStats — Wed, 18 Dec 2019 23:40:38 GMT

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.

Re: SUM function doesn't work on a basic CASE statement output?

atran2 — Wed, 18 Dec 2019 23:53:23 GMT

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?

Re: SUM function doesn't work on a basic CASE statement output?

PGStats — Thu, 19 Dec 2019 04:20:12 GMT

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.

Re: SUM function doesn't work on a basic CASE statement output?

Tom — Thu, 19 Dec 2019 04:46:34 GMT

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?