topic How to count by row group and columns in SAS Programming

How to count by row group and columns

marcus7w — Fri, 03 Jun 2022 18:25:04 GMT

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

Re: How to count by row group and columns

HB — Fri, 03 Jun 2022 18:42:50 GMT

This looks kind of like course work.

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

Re: How to count by row group and columns

marcus7w — Fri, 03 Jun 2022 18:58:20 GMT

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.

Re: How to count by row group and columns

PaigeMiller — Fri, 03 Jun 2022 20:20:34 GMT

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?

Re: How to count by row group and columns

marcus7w — Fri, 03 Jun 2022 20:44:36 GMT

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

Re: How to count by row group and columns

Tom — Fri, 03 Jun 2022 21:01:24 GMT

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;

Re: How to count by row group and columns

novinosrin — Fri, 03 Jun 2022 21:01:45 GMT

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;

Re: How to count by row group and columns

Tom — Fri, 03 Jun 2022 21:14:57 GMT

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

Re: How to count by row group and columns

novinosrin — Fri, 03 Jun 2022 21:22:13 GMT

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;

Re: How to count by row group and columns

marcus7w — Fri, 03 Jun 2022 23:27:18 GMT

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.

Re: How to count by row group and columns

marcus7w — Fri, 03 Jun 2022 23:31:37 GMT

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.