DATA Step, Macro, Functions and more

Need help condensing a data set

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

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: 19,787

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;

View solution in original post


All Replies
Super User
Posts: 19,787

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:

1B2..
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: 19,787

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 313 views
  • 3 likes
  • 2 in conversation