Alex Alex - 1 month ago 9
SQL Question

How to combine multiple single value outputs into single row in SAS?

I'm getting a count of a variable for multiple ranges and want to aggregate them all into a single row. Right now I aggregate them all into a table where the diagonals are populated but the off-diagonals are all empty. I want to keep the names I assign to each count as well. I haven't been able to find something online describing how to do this.

Here's a sample of my code.

PROC SQL;
SELECT COUNT(loannumber) AS FICO_sub_620
FROM tbl
WHERE LNFICO < 620

outer union

SELECT COUNT(loannumber) AS FICO_620_639
FROM tbl
WHERE LNFICO BETWEEN 620 AND 639

outer union

SELECT COUNT(loannumber) AS FICO_640_659
FROM tbl
WHERE LNFICO BETWEEN 640 AND 659

outer union

SELECT COUNT(loannumber) AS FICO_660_679
FROM tbl
WHERE LNFICO BETWEEN 660 AND 679;

RUN;


enter image description here

Answer

Use conditional aggregation:

PROC SQL;
    SELECT SUM(CASE WHEN LNFICO < 620 THEN 1 ELSE 0 END) AS FICO_sub_620,
           SUM(CASE WHEN LNFICO BETWEEN 620 AND 639 THEN 1 ELSE 0 END) AS FICO_620_639,
           SUM(CASE WHEN LNFICO BETWEEN 640 AND 659 THEN 1 ELSE 0 END) AS FICO_640_659,
           SUM(CASE WHEN LNFICO BETWEEN 660 AND 679 THEN 1 ELSE 0 END) AS FICO_660_679
    FROM tbl

RUN;
Comments