DATA Step, Macro, Functions and more

How to get the count of certain statuses in Datastep

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

How to get the count of certain statuses in Datastep

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

   


Accepted Solutions
Solution
‎11-24-2017 02:34 AM
Super User
Posts: 6,622

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

Posted in reply to UshaLatha

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;

 

View solution in original post


All Replies
Solution
‎11-24-2017 02:34 AM
Super User
Posts: 6,622

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

Posted in reply to UshaLatha

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;

 

Super Contributor
Super Contributor
Posts: 266

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

Posted in reply to UshaLatha

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.

 

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 77 views
  • 0 likes
  • 3 in conversation