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!
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
What have you already tried? What are your thoughts of how to approach this? Can you share some (may be not yet working) code?
Jag's code is working for me. Thanks!
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: