Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- How to count by row group and columns

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-03-2022 02:25 PM
(1817 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

This looks kind of like course work.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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)
group by answer;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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)
group by answer;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@marcus7w wrote:

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

Are you ready for the spotlight? We're accepting content ideas for **SAS Innovate 2025** to be held May 6-9 in Orlando, FL. The call is **open **until September 16. Read more here about **why** you should contribute and **what is in it** for you!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.