How to get the count of certain statuses in Datastep

[UshaLatha]

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

[Astounding · Accepted solution]

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;

[HB]

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.