BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
peppapig
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

7 REPLIES 7
overmar
Obsidian | Level 7

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.

Haikuo
Onyx | Level 15

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

Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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;

Haikuo
Onyx | Level 15

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

Haikuo

art297
Opal | Level 21

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

Art

p.s.  Happy New Year

RichardinOz
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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