Hi,
I have a dataset in the following format
ID Vi PID Complete Notes
100 1 29 1 good
100 2 34 0 early
101 2 29 1
101 3 32 0 bad
I want this to be formatted to
ID VI com_29 Note_29 Com_32 Note_32 Com_34 Note_34
100 1 1 good . . . .
100 2 . . . . 1 early
101 2 1 . . . . .
101 3 . . 0 bad . .
I'm currently here at my code;
data have1;
set a;
by id vi pid;
if first.id then line=0;
if first.pid then line+1;
varname = cats(pid,'_',line);
varname1 = cats(pid,'__',line);
run;
proc transpose data=have1 out=want1;
by id vi;
id varname;
var complete;
run;
proc transpose data=have1 out=want2;
by id vi;
id varname1;
var notes;
run;
proc sort data=want1;by id vi;Run;
proc sort data=want2;by id vi;Run;
data merge1;merge want1(in=x) want2(in=y);by id vi;if x and y;Run;
But since I'm keeping the count in the line variable for appeding it to the column name later, if a same person get a repeated PID value in a different visit the count is not the same, for example
ID VI PID COMPLETE NOTES
100 1 29 1
100 1 32 0
100 2 29 1
then my have1 dataset will have this varnames values which makes the want1 dataset to have two rows because of repeated 29_1 variable . So I need to stop the count at the last occurrence of the ID Vi and start a fresh count.Any help in here?
100 1 29_1
100 1 32_2
100 2 29_1
Thanks for your time.
Regards,
MAtt