Long to Wide

[deleted_user]

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

[LAP]

Not sure if this is exactly what you want but this may help

proc sort data = a nodup; by ID VI PID;
Data b (Keep = ID VI COM_29 Note_29 Com_32 Note_32 Com_34 Note_34);
attrib id vi length = 8
Com_29 length = 8
Note_29 length= $8
Com_32 length =8
Note_32 length= $8
Com_34 length= 8
Note_34 length= $8
;

array com (3) Com_29 Com_32 Com_34;
array note (3)$ Note_29 Note_32 Note_34;
do c = 1 to 3;
set a;
by ID VI;
Select (PID);
when (29) do; com(1) = Complete; note(1) = Notes; end;
when (32) do; com(2) = Complete; note(2) = Notes; end;
when (34) do; com(3) = complete; note(3) = Notes; end;
otherwise;
end;
if last.vi then return;
end;
run;


I included the attrib statement so that the order stays the same as in your example. You may have to get a bit more creative if you have more than the 3pairs of columns.

[deleted_user]

Thanks...

[Patrick]

Hi Matt

I believe the code below does what you asked for.

If I understand this right then you were very close and only missed the possibilities of the "copy" statement in "proc transpose".

data have;
infile datalines truncover;
input ID Vi PID Complete Notes $;
datalines;
100 1 29 1 good
100 2 34 0 early
101 2 29 1
101 3 32 0 bad
;
run;

proc transpose data=have out=pre_want(drop=_name_) prefix=Com_;
by id vi;
id pid;
var Complete;
copy pid Notes;
run;

proc transpose data=pre_want out=want(drop=_name_) prefix=Note_;
by id vi;
id pid;
var Notes;
copy Com_:;
run;

proc print data=want noobs;
run;


HTH
Patrick

[deleted_user]

Thanks Patrick. This is what I was exactly looking for...