keep selected records and count

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

keep selected records and count

Data Have:

IDTIMEQ_TYPEQ1Q2Q3Q4Q05Q06Q07Q08Q09Q10
A011A33
A012A44
A013A55
A014A 88
A015A 66
A016A 77
A017A 22
A018A 11
A021B11 2 2 2
A031C22 1122 225588

Data Want:

ID TIMEQ_TYPEQ1Q2Q3Q4Q05Q06Q07Q08Q09Q10COUNT
A013A55
A016A 77
A018A 11 3
A021B 1 2 25
A031C22 1122 2255886

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!


Accepted Solutions
Solution
‎01-04-2014 09:29 AM
Trusted Advisor
Posts: 1,137

Re: keep selected records and count

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

Thanks,
Jag

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

Re: keep selected records and count

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

Contributor
Posts: 42

Re: keep selected records and count

Jag's code is working for me. Thanks!

Solution
‎01-04-2014 09:29 AM
Trusted Advisor
Posts: 1,137

Re: keep selected records and count

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

Thanks,
Jag
Respected Advisor
Posts: 3,156

Re: keep selected records and count

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

🔒 This topic is solved and locked.

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

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