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

[atran2]

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?

[PGStats]

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.

[atran2]

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?

[PGStats]

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.

[Tom]

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?