keep selected records and count

[peppapig]

Data Have:

ID

TIME

Q_TYPE

Q1

Q2

Q3

Q4

Q05

Q06

Q07

Q08

Q09

Q10

A01

1

A

33

A01

2

A

44

A01

3

A

55

A01

4

A

88

A01

5

A

66

A01

6

A

77

A01

7

A

22

A01

8

A

11

A02	1	B	1	1		2		2				2
A03	1	C	22		11	22				22	55	88

Data Want:

ID

TIME

Q_TYPE

Q1

Q2

Q3

Q4

Q05

Q06

Q07

Q08

Q09

Q10

COUNT

A01

3

A

55

A01

6

A

77

A01

8

A

11

3

A02	1	B		1				2				2	5
A03	1	C	22		11	22				22	55	88	6

For each question  answered, keep the last answer according to the time, then count how many questions total answered for each Q_type.

Thank you!

[Patrick]

What have you already tried? What are your thoughts of how to approach this? Can you share some (may be not yet working) code?

[peppapig]

Jag's code is working for me. Thanks!

[Jagadishkatam · Accepted solution]

Please try the below code, producing the expecting output

consider that you have data in the dataset have.

proc sort data=have;

    by id q_type time;

run;

%macro test(q);

data &q;

    set have;

    by     id q_type time;

    if &q. ne .;

    keep id time q_type &q.;

run;

data &q._;

    set &q.;

        by      id q_type time;

        if last.q_type;

    run;

%mend;

%macro test2;

data _null_;

%do i = 1 %to 10;

%test(q&i);

%end;

run;

%mend;

%test2;

data count;

    merge q1_ q2_ q3_ q4_ q5_ q6_ q7_ q8_ q9_ q10_;

    by          id q_type ;

    count = 10- nmiss(of q1-q10);

    keep  id q_type time count;

run;

data all;

    merge q1_ q2_ q3_ q4_ q5_ q6_ q7_ q8_ q9_ q10_ count;

    by id q_type time;

run;

Thanks,

Jag

[Haikuo]

Here is one approach using Hash. If your RAM is not big enough to hold the whole table, some variation could be designed to load only one ID each time.

data have ;

  input id $ q_type $ q1-q10 ;

  time+1+(lag(id) ne id)*(-1)*time;

cards;

A01 A 33 . . . . . . . . .

A01 A 44 . . . . . . . . .

A01 A 55 . . . . . . . . .

A01 A . . . 88 . . . . . .

A01 A . . . 66 . . . . . .

A01 A . . . 77 . . . . . .

A01 A . . . . . . 22 . . .

A01 A . . . . . . 11 . . .

A02 B 1 1 . 2 . 2 . . . 2

A03 C 22 . . 11 22 . . 22 55 88

run;

data want;

  if _n_=1 then do;

    declare hash h(dataset:'have');

      h.definekey('id','time');

      h.definedata(all:'y');

      h.definedone();

      declare hash _t(ordered:'a');

      _t.definekey('time');

      _t.definedata('time');

      _t.definedone();

      declare hiter _ti('_t');

  end;

  do until (last.id);

    set have;

        by id;

         array q q1-q10;

         array t(10) _temporary_;

         do over q;

          if not missing(q) then t(_i_)=time;

         end;

         if last.id then do;

ct=n(of t(*));

         do _i_=1 to 10;

if t(_i_) then do; time=t(_i_); rc=_t.replace(); end;

         end;

          do rc=1 to _t.num_items;

             _ti.next();

h.find();

             if rc= _t.num_items then count=ct;

             output;

            end;

            end;

      end;

      call missing (of t(*));

      rc=_ti.next();

      rc=_t.clear();

      drop ct rc;

run;

Haikuo