☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to count by row group and columns

Sorry I'm new to coding, so I appreciate the help in advance.

I'm trying to answer these questions based on the table below containing 30 day delinquency indicator by product by person:

How many people have one product that is 30 days delinquent?

How many people have two products that is 30 days delinquent?

How many people have three products that is 30 days delinquent?

How many people have zero products that is 30 days delinquent?

 Products ID Car Boat House Joe 30 Joe 30 Karen Matt 30 Matt 30 Matt 30 john 30 Mike 30 Mike 30 Tony 30 Tony 30 Tony 30

How would I code this in SAS to get the answer

 answer one product 1 two product 2 three products 2 zero products 1
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to count by row group and columns

@marcus7w wrote:

Maybe I worded it incorrrectly Joe is delinquent on his car loan and his mortgage,  so that would be 2 products that Joe is delinquent  and  Matt is delinquent on all 3  of his products.   The question i'm being asking,  if a customer has more than one product at the bank,  how many  products are they're delinquent on.   I want respond we have x number customer that is delinquent on two products,   x number customer three products etc

So your input is in wrong format for storing the information.  It will not adjust to the number of different products that the bank might decide to offer.

You should have something like:

``````data have;
input person \$ product \$ days over30 ;
cards;
joe car 10 0
joe boat 0 0
sam car 35 1
sam house 60 1
sally . . 0
;``````

So you can then count/sum the number.

``````proc sql;
create table want as
select n_fail,count(*) as n_persons  from
(
select person,sum(over30) as n_fail
from have group by person
)
group by n_fail
;
quit;``````
10 REPLIES 10
Barite | Level 11

## Re: How to count by row group and columns

This looks kind of like course work.

What have you tried?  Can you post the data as a working data step?

Obsidian | Level 7

## Re: How to count by row group and columns

no it's not a course work.  It's a bank portfolio project I'm working on. I've been stuck for hours.  I just simplify the example to ask the question.

proc sql;

create table test as

selected

id,

product,

sum(delq)

from test_30

where delq >=30

group by 1, 2;

Where i'm going wrong,  i tried to do a proc transpose

proc transpose data=test out=test2;

by id;

id product;

var delq;

run;

I thought maybe I could do If Then statements combination, but there so many products, i would have to do every combination.

Diamond | Level 26

## Re: How to count by row group and columns

So each delinquency is always on its own observation? There is never a case where there are more than one delinquency on a single observation?

--
Paige Miller
Obsidian | Level 7

## Re: How to count by row group and columns

Maybe I worded it incorrrectly Joe is delinquent on his car loan and his mortgage,  so that would be 2 products that Joe is delinquent  and  Matt is delinquent on all 3  of his products.   The question i'm being asking,  if a customer has more than one product at the bank,  how many  products are they're delinquent on.   I want respond we have x number customer that is delinquent on two products,   x number customer three products etc

Tourmaline | Level 20

## Re: How to count by row group and columns

Not a very good design of your source data, but if you are certain that's your input-

``````
data have;
input ID : \$10.	Car	Boat	House;
cards;
Joe	30	.	.
Joe	.	.	30
Karen	.	.	.
Matt	.	.	30
Matt	.	30	 .
Matt	30	.	.
john	30	.	.
Mike	30	.	.
Mike	.	.	30
Tony	.	.	30
Tony	.	30	.
Tony	30	.	.
;

proc sql;
create table want as
select answer, count(id) as count
from (select id, sum(n(car, boat, house)) as answer from have  group by id)
quit;``````

Tourmaline | Level 20

## Re: How to count by row group and columns

Or better-

``````
data have;
input ID : \$10.	Car	Boat	House;
cards;
Joe	30	.	.
Joe	.	.	30
Karen	.	.	.
Matt	.	.	30
Matt	.	30	 .
Matt	30	.	.
john	30	.	.
Mike	30	.	.
Mike	.	.	30
Tony	.	.	30
Tony	.	30	.
Tony	30	.	.
;
proc sql;
create table want as
select n(car,boat, house) as answer, count(id) as count
from (select id, max(car) as car,max( boat) as boat,max( house) as house from have  group by id)
quit;``````
Obsidian | Level 7

## Re: How to count by row group and columns

You correct the source data wasn't correct, it was after I did proc transpose.  The bank data is set as id, product,  delq.

Thank you for the help much appreciated.    I'm working with your solution now.

Super User

## Re: How to count by row group and columns

@marcus7w wrote:

Maybe I worded it incorrrectly Joe is delinquent on his car loan and his mortgage,  so that would be 2 products that Joe is delinquent  and  Matt is delinquent on all 3  of his products.   The question i'm being asking,  if a customer has more than one product at the bank,  how many  products are they're delinquent on.   I want respond we have x number customer that is delinquent on two products,   x number customer three products etc

So your input is in wrong format for storing the information.  It will not adjust to the number of different products that the bank might decide to offer.

You should have something like:

``````data have;
input person \$ product \$ days over30 ;
cards;
joe car 10 0
joe boat 0 0
sam car 35 1
sam house 60 1
sally . . 0
;``````

So you can then count/sum the number.

``````proc sql;
create table want as
select n_fail,count(*) as n_persons  from
(
select person,sum(over30) as n_fail
from have group by person
)
group by n_fail
;
quit;``````
Obsidian | Level 7

## Re: How to count by row group and columns

You're correct the 'have" data i posted was incorrect, it was after I did the proc tranpose.   What you post is how the bank data is arranged.

Thank for the solution, this is very helpful.  learned alot.

Super User

## Re: How to count by row group and columns

Count number of products per observation.

Then summaries by number of products.

``````data want;
set have;
n_products= n(of car boat house);
run;
proc freq data=want;
tables n_products / list;
run;``````
Discussion stats
• 10 replies
• 1818 views
• 3 likes
• 5 in conversation