topic Re: Need help condensing a data set in SAS Programming

Need help condensing a data set

spirto — Mon, 12 May 2014 20:17:47 GMT

Hello my current dataset is in the form below (dummy data):

ID	Visit	Group1	ScoreX	Group2	ScoreY	Group3	ScoreZ
1	1	A	0	B	0	C	0
1	2	A	0	B	1	C	0
1	3	A	1	B	1	C	0
1	4	A	2	B	1	C	0
2	1	C	1	A	0	B	0
2	2	C	0	A	1	B	1
2	3	C	0	A	3	B	2
2	4	C	3	A	3	B	3

and would like to convert into two ways: 1) i would like to go from wide to long format ***UPDATE part 1 solved: see post 3 below***

ID	Group	Visit	Score
1	A	1	0
1	A	2	0
1	A	3	1
1	A	4	2
1	B	1	0
1	B	2	1
1	B	3	1
1	B	4	1
1	C	1	0
1	C	2	0
1	C	3	0
1	C	4	0
2	B	1	0
2	B	2	0
2	B	3	0
2	B	4	3
2	A	1	0
2	A	2	1
...	(continued)	...	...

and 2) I would like to distill the data into this below. ***UPDATE part 2 solved: see post 4 and 5 below***

Essentially I am counting the number of visits it took to get a score of 1, 2, or 3.

ID	Group	Visit of first 1 score	Visit of first 2 score	Visit of first 3 score
1	A	3	4	.
1	B	2	.	.
1	C	.	.	.
2	A	2	.	3
2	B	2	3	4
2	C	1	.	4

I believe I can do this with a combination of arrays but I am not sure how to handle the groups when my data is in wide format (1st table). So my thoughts are first I need to get it into long format (2nd table) and then compress the data (3rd table). I do not know SQL so any datastep programming help would be greatly appreciated

Re: Need help condensing a data set

Reeza — Mon, 12 May 2014 20:34:24 GMT

Does your data from your second table match up with the first/second table?

Specifically, where does this line come from:

Re: Need help condensing a data set

spirto — Mon, 12 May 2014 20:41:34 GMT

Reeza,

so the third table used the data from the first/second table. So to answer your question, for the first subject ID=1 group=B, it took two visits to get a score of 1 but that subject never got a score of 2 or 3 so I left them blank. Another example would be for ID=2 group=A, a score of 1 was achieved on visit 2, a score of 2 was never attained (so its missing) and a score of 3 was achieved on visit 3

Re: Need help condensing a data set

spirto — Tue, 13 May 2014 03:33:23 GMT

Ok I was able to figure out how to convert from the wide format to the long format with the code below. Half way there!

data TABLE1;

input ID Visit Group1 $ ScoreX Group2 $ ScoreY Group3 $ ScoreZ;

datalines;

1 1 A 0 B 0 C 0

1 2 A 0 B 1 C 0

1 3 A 1 B 1 C 0

1 4 A 2 B 1 C 0

2 1 C 1 A 0 B 0

2 2 C 0 A 1 B 1

2 3 C 0 A 3 B 2

2 4 C 3 A 3 B 3

;

data TABLE2(keep=id group visit score);

retain id group visit; set TABLE1;

array grp{3} Group1 Group2 Group3;

array scr{3} ScoreX ScoreY ScoreZ;

do i=1 to 3;

group=grp{i};

score=scr{i};

output;

end;

run;

proc sort data=TABLE2; by id group visit; run;

Re: Need help condensing a data set

Reeza — Tue, 13 May 2014 03:49:56 GMT

UNTESTED (and written while watching TV)

data want;

set table2;

by id group;

retain flag1-flag3 visit_score1-visit_score3 counter;

if first.group then do;

visit_score1=.; visit_score2=.; visit_score3=.;

flag1=0; flag2=0;flag3=0;

counter=0;

end;

counter+1;

if score=1 and flag1=0 then do;

visit_score1=counter; flag1=1;

end;

else if score=2 and flag2=0 then do;

visit_score2=counter; flag2=1;

end;

else if score=3 and flag3=0 then do;

visit_score3=counter; flag3=1;

end;

if last.group then output;

run;

Re: Need help condensing a data set

spirto — Tue, 13 May 2014 15:09:13 GMT

Awesome thank you Reeza! I found out that you can also do this with array statements as below:

data TABLE3(keep=id group visit_score1-visit_score3); set TABLE2;

by id group;

array vs{3} visit_score1-visit_score3 (3*0);

array flag{3} flag1-flag3 (3*0);

if first.group then do;

do i=1 to 3;

vs{i}=.; flag{i}=0;

end;

counter=0;

end;

counter+1;

do i=1 to 3;

if score=i and flag{i}=0 then do;

vs{i}=counter; flag{i}=1;

end;

if last.group then output;

run;

ID	Visit	Group1	ScoreX	Group2	ScoreY	Group3	ScoreZ
1	1	A	0	B	0	C	0
1	2	A	0	B	1	C	0
1	3	A	1	B	1	C	0
1	4	A	2	B	1	C	0
2	1	C	1	A	0	B	0
2	2	C	0	A	1	B	1
2	3	C	0	A	3	B	2
2	4	C	3	A	3	B	3

ID	Visit	Group1	ScoreX	Group2	ScoreY	Group3	ScoreZ
1	1	A	0	B	0	C	0
1	2	A	0	B	1	C	0
1	3	A	1	B	1	C	0
1	4	A	2	B	1	C	0
2	1	C	1	A	0	B	0
2	2	C	0	A	1	B	1
2	3	C	0	A	3	B	2
2	4	C	3	A	3	B	3

ID	Visit	Group1	ScoreX	Group2	ScoreY	Group3	ScoreZ
1	1	A	0	B	0	C	0
1	2	A	0	B	1	C	0
1	3	A	1	B	1	C	0
1	4	A	2	B	1	C	0
2	1	C	1	A	0	B	0
2	2	C	0	A	1	B	1
2	3	C	0	A	3	B	2
2	4	C	3	A	3	B	3