I am obtaining values from two tables. When I run the subquery in its own PROC SQL statement in SAS, it runs fine, with the count of citations for each ID. When I input the subquery into my SELECT outer query, it gives me ERROR: Subquery evaluated to more than one row. I am having a hard time determining the cause of this issue.
The subquery should result in one row of count of citations per ID. I am trying to get the count of citations (per ID) into my outer query. Not all items from B will be in A (hence the left join on B).
SELECT
A.AREA
,A.NAME
,B.ID
,(
SELECT
COUNT(B.TYPE)
FROM
EVAL.CITATIONS AS B
GROUP BY
B.ID
)
AS COUNT_CITATIONS
FROM
EVAL.OCT AS A
LEFT JOIN EVAL.CITATIONS
ON A.DBA = B.NAME
ORDER BY A.NAME ASC
;
I expected the outer query to pull the counts for the citations per ID. The citations are coming from table B (which I’m using to left join into table A). I have been searching forums for this error and I understand that my query is resulting in more than one row, but I can’t figure out why the outer query is not simply pulling the counts I need from ID when the left join completes.
I also tried adding in the subquery this WHERE clause after researching some similar questions to no avail.
WHERE FACID = CDPH_CITATIONS.FACID
>Solution :
You need to use a correlated subquery where your subquery references your main query e.g.
SELECT
A.AREA
,A.NAME
,B.ID
,(
SELECT
COUNT(C.TYPE)
FROM
EVAL.CITATIONS AS C
WHERE B.ID = C.ID
GROUP BY
C.ID
)
AS COUNT_CITATIONS
FROM
EVAL.OCT AS A
LEFT JOIN EVAL.CITATIONS
ON A.DBA = B.NAME
ORDER BY A.NAME ASC
However, I don’t think you need a subquery at all, you can just count the B records and group by the other columns in your main query