CODEWITHSUNDEEP
sqlformattingsasoutput
Alex F
Alex F

Reputation: 2274

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

Upvotes: 0

Views: 116

Answers (2)

Joe
Joe

Reputation: 63424

I hope that's not how you're actually doing this, because that's not an appropriate method to do the aggregation.

That said, if there's a valid reason to do what you are doing, the way you can combine them is through a self-update. This collapses the rows into one.

data have;
  input id x y z;
datalines;
1 1 . .
1 . 2 .
1 . . 3
2 4 . .
2 . 5 .
2 . . 6
;;;;
run;

data want;
  update have(obs=0) have;
  by id;
run;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 1

Related Questions