topic Re: keep selected records and count in SAS Programming

keep selected records and count

peppapig — Fri, 03 Jan 2014 20:53:32 GMT

Data Have:

TIME

Q_TYPE

Q05

Q06

Q07

Q08

Q09

Q10

A01

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

Data Want:

TIME

Q_TYPE

Q05

Q06

Q07

Q08

Q09

Q10

COUNT

A01

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!

Re: keep selected records and count

Patrick — Sat, 04 Jan 2014 00:50:34 GMT

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

Re: keep selected records and count

Jagadishkatam — Sat, 04 Jan 2014 14:29:16 GMT

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

Re: keep selected records and count

Haikuo — Mon, 06 Jan 2014 20:34:18 GMT

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;

call missing (of t(*));

rc=_ti.next();

rc=_t.clear();

drop ct rc;

run;

Haikuo

Re: keep selected records and count

peppapig — Wed, 08 Jan 2014 23:15:27 GMT

Jag's code is working for me. Thanks!