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:

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

4 REPLIES 4
Patrick
Opal | Level 21

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
Calcite | Level 5

Jag's code is working for me. Thanks!

Jagadishkatam
Amethyst | Level 16

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
Haikuo
Onyx | Level 15

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

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
  • 4 replies
  • 642 views
  • 3 likes
  • 4 in conversation