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 |
@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;
This looks kind of like course work.
What have you tried? Can you post the data as a working data step?
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.
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?
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
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;
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;
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.
@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;
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.
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!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: