Solved
Contributor
Posts: 39

Need help condensing a data set

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

IDVisitGroup1ScoreXGroup2ScoreYGroup3ScoreZ
11A0B0C0
12A0B1C0
13A1B1C0
14A2B1C0
21C1A0B0
22C0A1B1
23C0A3B2
24C3A3B3

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***

IDGroupVisitScore
1A1

0

1A20
1A31
1A42
1B10
1B21
1B31
1B41
1C10
1C20
1C30
1C40
2B10
2B20
2B30
2B43
2A10
2A21
...(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.

IDGroupVisit of first 1 scoreVisit of first 2 scoreVisit of first 3 score
1A34.
1B2..
1C...
2A2.3
2B234
2C1.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

Accepted Solutions
Solution
‎05-12-2014 11:49 PM
Super User
Posts: 23,776

Re: Need help condensing a data set

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;

All Replies
Super User
Posts: 23,776

Re: Need help condensing a data set

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

Specifically, where does this line come from:

 1 B 2 . .
Contributor
Posts: 39

Re: Need help condensing a data set

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

Contributor
Posts: 39

Re: Need help condensing a data set

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;

Solution
‎05-12-2014 11:49 PM
Super User
Posts: 23,776

Re: Need help condensing a data set

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;

Contributor
Posts: 39

Re: Need help condensing a data set

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;

end;

if last.group then output;

run;

🔒 This topic is solved and locked.