how to count

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

how to count

Data Have:

IDQ_TYPEQ1Q2Q3Q4Q5 Q6Q7Q8Q9Q10
A01ANULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
A01A1NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01A2NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01ANULLNULLNULL6NULLNULLNULLNULLNULLNULL
A01ANULLNULLNULL7NULLNULLNULLNULLNULLNULL
A01ANULLNULLNULL4NULLNULLNULLNULLNULLNULL
A01ANULLNULLNULLNULLNULLNULL3NULLNULLNULL
A01ANULLNULLNULLNULLNULLNULL4NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULL5NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULL7NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULL8NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
A01BNULLNULLNULLNULL5NULLNULLNULLNULLNULL
A01BNULLNULLNULLNULL6NULLNULLNULLNULLNULL
A01BNULLNULLNULLNULL7NULLNULLNULLNULLNULL
A01BNULLNULLNULL3NULLNULLNULLNULLNULLNULL
A01BNULLNULLNULL4NULLNULLNULLNULLNULLNULL
A01C1NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01C2NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01C3NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01CNULLNULL5NULLNULLNULLNULLNULLNULLNULL
A01CNULLNULL6NULLNULLNULLNULLNULLNULLNULL
A01CNULLNULLNULLNULLNULLNULLNULLNULLNULL5
A01CNULLNULLNULLNULLNULLNULLNULLNULLNULL3
A01CNULLNULLNULLNULLNULLNULLNULL4NULLNULL
A01CNULLNULLNULLNULLNULLNULLNULL5NULLNULL
A01CNULLNULLNULLNULLNULLNULLNULL6NULLNULL
A01CNULLNULLNULLNULLNULL7NULLNULLNULLNULL
A01CNULLNULLNULLNULLNULL8NULLNULLNULLNULL
A01CNULLNULLNULLNULLNULL9NULLNULLNULLNULL
A01CNULLNULLNULLNULL7NULLNULLNULLNULLNULL
A01CNULLNULLNULLNULL5NULLNULLNULLNULLNULL
A01CNULLNULLNULLNULL4NULLNULLNULLNULLNULL
A02DNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
A03ENULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

DATA WANT:

IDQ_TYPEQ1Q2Q3Q4Q5 Q6Q7Q8Q9Q10COUNT
A01ANULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
A01A1NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01A2NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01ANULLNULLNULL6NULLNULLNULLNULLNULLNULL
A01ANULLNULLNULL7NULLNULLNULLNULLNULLNULL
A01ANULLNULLNULL4NULLNULLNULLNULLNULLNULL
A01ANULLNULLNULLNULLNULLNULL3NULLNULLNULL
A01ANULLNULLNULLNULLNULLNULL4NULLNULLNULL3
A01BNULLNULLNULLNULLNULLNULL5NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULL7NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULL8NULLNULLNULL
A01BNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
A01BNULLNULLNULLNULL5NULLNULLNULLNULLNULL
A01BNULLNULLNULLNULL6NULLNULLNULLNULLNULL
A01BNULLNULLNULLNULL7NULLNULLNULLNULLNULL
A01BNULLNULLNULL3NULLNULLNULLNULLNULLNULL
A01BNULLNULLNULL4NULLNULLNULLNULLNULLNULL3
A01C1NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01C2NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01C3NULLNULLNULLNULLNULLNULLNULLNULLNULL
A01CNULLNULL5NULLNULLNULLNULLNULLNULLNULL
A01CNULLNULL6NULLNULLNULLNULLNULLNULLNULL
A01CNULLNULLNULLNULLNULLNULLNULLNULLNULL5
A01CNULLNULLNULLNULLNULLNULLNULLNULLNULL3
A01CNULLNULLNULLNULLNULLNULLNULL4NULLNULL
A01CNULLNULLNULLNULLNULLNULLNULL5NULLNULL
A01CNULLNULLNULLNULLNULLNULLNULL6NULLNULL
A01CNULLNULLNULLNULLNULL7NULLNULLNULLNULL
A01CNULLNULLNULLNULLNULL8NULLNULLNULLNULL
A01CNULLNULLNULLNULLNULL9NULLNULLNULLNULL
A01CNULLNULLNULLNULL7NULLNULLNULLNULLNULL
A01CNULLNULLNULLNULL5NULLNULLNULLNULLNULL
A01CNULLNULLNULLNULL4NULLNULLNULLNULLNULL6
A02DNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL0
A03ENULLNULLNULLNULLNULLNULLNULLNULLNULLNULL0

Variable Q_type is Questionnaire Type, each questionnaire could have 10 questions or less, whatever answered, are listed there as some 1-10 values, otherwise are NULL.

Want to count for each ID, each questionnaire type, how many questions were answered. There are cases for same ID, same questionnaire answered several times, then keep the last answered one as final answer, that is, only add 1 to total count. 

Hope I explained my question clearly enough.

Thank you,and happy new year!


Accepted Solutions
Solution
‎01-02-2014 08:49 PM
Respected Advisor
Posts: 3,124

Re: how to count

Here is an one step data step approach:

data want;

  set have;

    by id q_type;

  array q q1-q10;

  array _q(1:10)  _temporary_;

  if first.q_type then do;

       ct=0;

     do i=1 to dim(_q);

   _q(i)=1;

  end;

  end;

   do i=1 to dim(q);

   ct+(q(i) ne 'NULL')*_q(i);

   if q(i) ne 'NULL' then _q(i)=0;

      end;

  count=ifn(last.q_type,ct,.);

  drop i ct;

run;

Haikuo

View solution in original post


All Replies
Frequent Contributor
Posts: 83

Re: how to count

Ok so the way that I understand this is that you want the ID variable, questionnaire type, and the number of variables that they answered. So essentially your final table should only include 3 variables, ID, Questionnaire type, and count. Also I didn't really understand your comment about adding 1 to total, so I can't really address that.

To do this you have a couple of options, but I think that the easiest and fastest way is to use both SQL and datastep.

proc sql;

create table good as

select distinct ID, q_type, (max(Q1)) as Q1, (max(Q2)) as Q2, (max(Q3)) as Q3, (max(Q4)) as Q4, (max(Q5)) as Q5,

(max(Q6)) as Q1, (max(Q7)) as Q7, (max(Q8)) as Q8, (max(Q9)) as Q9, (max(Q10)) as Q10

from bad

group by ID, q_type;

quit;

/*The sql code takes the maximum value of any of the responses which will leave a null value for those which only have null values*/

data good;

     set good;

     array Que (10) Q1-Q10;

     do i = 1 to 10;

     if que(i) = . then que(i) = 0;

     end;

     count = sum(Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10);

     keep id q_type count;

run;

/*Converting all of these to 0's just allows sum to do its job without a whole lot of issues, and then you can just keep the three variables that you wanted*/

If you actually wanted values for the responses then other code would need to be written.

Solution
‎01-02-2014 08:49 PM
Respected Advisor
Posts: 3,124

Re: how to count

Here is an one step data step approach:

data want;

  set have;

    by id q_type;

  array q q1-q10;

  array _q(1:10)  _temporary_;

  if first.q_type then do;

       ct=0;

     do i=1 to dim(_q);

   _q(i)=1;

  end;

  end;

   do i=1 to dim(q);

   ct+(q(i) ne 'NULL')*_q(i);

   if q(i) ne 'NULL' then _q(i)=0;

      end;

  count=ifn(last.q_type,ct,.);

  drop i ct;

run;

Haikuo

Super User
Super User
Posts: 6,497

Re: how to count

This might be an issue that could use the UPDATE statement.  It will allow you to collapse all of the variables to the last non-missing value.

data have ;

  input id $ q_type $ q1-q10 ;

cards;

A01 A . . . . . . . . . .

A01 A 1 . . . . . . . . .

A01 A 2 . . . . . . . . .

A01 A . . . 6 . . . . . .

A01 A . . . 7 . . . . . .

A01 A . . . 4 . . . . . .

A01 A . . . . . . 3 . . .

A01 A . . . . . . 4 . . .

A01 B . . . . . . 5 . . .

A01 B . . . . . . 7 . . .

A01 B . . . . . . 8 . . .

A01 B . . . . . . . . . .

A01 B . . . . 5 . . . . .

A01 B . . . . 6 . . . . .

A01 B . . . . 7 . . . . .

A01 B . . . 3 . . . . . .

A01 B . . . 4 . . . . . .

A01 C 1 . . . . . . . . .

A01 C 2 . . . . . . . . .

A01 C 3 . . . . . . . . .

A01 C . . 5 . . . . . . .

A01 C . . 6 . . . . . . .

A01 C . . . . . . . . . 5

A01 C . . . . . . . . . 3

A01 C . . . . . . . 4 . .

A01 C . . . . . . . 5 . .

A01 C . . . . . . . 6 . .

A01 C . . . . . 7 . . . .

A01 C . . . . . 8 . . . .

A01 C . . . . . 9 . . . .

A01 C . . . . 7 . . . . .

A01 C . . . . 5 . . . . .

A01 C . . . . 4 . . . . .

A02 D . . . . . . . . . .

A03 E . . . . . . . . . .

run;

data counts ;

  update have(obs=0) have ;

  by id q_type ;

  if last.q_type then count=n(of q1-q10);

  output;

  keep count ;

run;


data want ;

  merge have counts;

  * NO BY STATEMENT ;

run;

PROC Star
Posts: 7,356

Re: how to count

I think that Haikuo's code does what you want.  The following, methinks, is simply a slightly simpler version of the same approach:

data want;

  set have;

  by id q_type;

  array q q1-q10;

  array _q(10)  _temporary_;

  if first.q_type then do;

    call missing(of _q(*));

  end;

  do _n_=1 to dim(q);

    if q(_n_) then _q(_n_)=1;

  end;

  if last.q_type then count=max(0,sum(of _q(*)));

run;

Respected Advisor
Posts: 3,124

Re: how to count

Definitely nicer approach, Art! I think you may not need "retain _q:;" as temporary array is retained by default?

Haikuo

PROC Star
Posts: 7,356

Re: how to count

: Guess what: I don't know everything and just learned something new.  Yes, you are indeed correct!

Art

p.s.  Happy New Year

Super Contributor
Posts: 644

Re: how to count

Art

I think there is an argument for retaining the retain as an explicit statement in this case.  As the statement is interpreted at compile time it does not make the code any less efficient but it does act to signal (to anyone who has to maintain the code) that the value is being retained. 

Richard now back in NZ

And a prosperous New Year to you

PS was stoked at the poke in your Christmas joke

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 410 views
  • 6 likes
  • 6 in conversation