Tallying instances of an indicator efficiently

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Tallying instances of an indicator efficiently

Hey Everyone,

I'm trying to tally the number of times an indicator occurs per customer.

The complication is the size of the database. It is a yearly table with end of month data for each year. So any given customer can be in the table max. 12 times and we have a substantial quantity of customers.

The data is laid out as such (simplified):

ym_id      cust_id      ind_1

201411      1                0

201411      2                1

201411      3                0

201410      1                1

201410      2                1

201410      3                0

201409      1                1

201409      2                1

201409      3                0

     .           .                 .

     .           .                 .

     .           .                 .

So in the end I would like the output to be:

cust_id      ind_count

     1               2

     2               3

     3               0

The main issue is doing this efficiently as the database is likely over 50 million rows.

Thank you every one for their help.


Accepted Solutions
Solution
‎06-30-2015 11:10 AM
Valued Guide
Posts: 854

Re: Tallying instances of an indicator efficiently

data have;

input ym_id      cust_id      ind_1;

cards;

201411      1                0

201411      2                1

201411      3                0

201410      1                1

201410      2                1

201410      3                0

201409      1                1

201409      2                1

201409      3                0

;

run;

proc sql;

create table want as

select cust_id,sum(ind_1) as ind_count

from have

group by cust_id;

View solution in original post


All Replies
Solution
‎06-30-2015 11:10 AM
Valued Guide
Posts: 854

Re: Tallying instances of an indicator efficiently

data have;

input ym_id      cust_id      ind_1;

cards;

201411      1                0

201411      2                1

201411      3                0

201410      1                1

201410      2                1

201410      3                0

201409      1                1

201409      2                1

201409      3                0

;

run;

proc sql;

create table want as

select cust_id,sum(ind_1) as ind_count

from have

group by cust_id;

Grand Advisor
Posts: 10,223

Re: Tallying instances of an indicator efficiently

proc summary data=have nway;

     class cust_id;

     var ind_1 ;

     output out=want (drop=_type_ _freq_) sum=ind_count;

run;

You could also ask for Mean and the value would be percent: Mean = Ind_percent;

If there are a bunch of this indicators put them all on the VAR line and use  sum= /autoname which would create a bunch of variables with _sum appended to the original variable name (barring very long variable name issues).

Occasional Contributor
Posts: 9

Re: Tallying instances of an indicator efficiently

Thanks! I tried out this way too. It's great to have a couple ways to tackle the same problem.

Occasional Contributor
Posts: 9

Re: Tallying instances of an indicator efficiently

Thanks so much. I managed to figure it out that way too so thanks for confirming I did it right!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 291 views
  • 3 likes
  • 3 in conversation