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

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
IDCarBoatHouse
Joe30  
Joe  30
Karen   
Matt  30
Matt 30 
Matt30  
john30  
Mike30  
Mike  30
Tony  30
Tony 30 
Tony30  

 

How would I code this in SAS to get the answer

answer 
one product1
two product2
three products2
zero products1
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

10 REPLIES 10
HB
Barite | Level 11 HB
Barite | Level 11

This looks kind of like course work.

 

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

marcus7w
Obsidian | Level 7

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.

 

 

 

PaigeMiller
Diamond | Level 26

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
marcus7w
Obsidian | Level 7

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

 

 

novinosrin
Tourmaline | Level 20

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;

 

novinosrin
Tourmaline | Level 20

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;
marcus7w
Obsidian | Level 7

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.  

Tom
Super User Tom
Super User

@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;
marcus7w
Obsidian | Level 7

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.  

Tom
Super User Tom
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 2385 views
  • 3 likes
  • 5 in conversation