topic Re: How to get the count of certain statuses in Datastep in SAS Programming

How to get the count of certain statuses in Datastep

UshaLatha — Tue, 21 Nov 2017 15:50:43 GMT

Hi all,

I am working on a sas dataset, where I need to get the count of certain statuses ('3' or '6').

My input data is:

loan_id month STAT

1234 1 0

1234 2 0

1234 3 3

1234 4 6

1234 5 6

Output should be:

loan_id month STAT count

1234 1 0 0

1234 2 0 0

1234 3 3 1

1234 4 6 2

1234 5 3 3

Whenever the status is either 3 or 6, the count should be incremented.

Please help me with this.

Thanks

Re: How to get the count of certain statuses in Datastep

Astounding — Tue, 21 Nov 2017 16:25:47 GMT

This version assumes you want a separate count for each LOAN_ID:

data want;

set have;

by loan_id;

if first.loan_id then count=0;

if stat in (3, 6) then count + 1;

run;

Re: How to get the count of certain statuses in Datastep

HB — Tue, 21 Nov 2017 17:44:47 GMT

As an alternative and if you are wanting more of a summary, you could use PROC SQL

data loan_status;
   input loan_id  month STAT ;
datalines;
1234    1	0
1234    2   0
1234    3   3
1234    4   6
1234    5   6
2345	1	3
2345	2	6
2345	3	0
3425	4	6
3423	5	0
;
run;


proc sql;
	create table status_count as
	select loan_id, count(stat) as mycount
	from loan_status
	where stat in (3,6)
	group by loan_id
	order by loan_id;
quit;

This would give you

The SAS System

loan_id	mycount
1234	3
2345	2
3425	1

Just presenting an alternative.