Contributor
Posts: 34

# Summarizing across variables

data split (drop= i);

set roster;

if activities="," then actual_activities="1,";

IF status="" then do;

activities=prxchange('s/([0-9])([,A-Za-z])/\$1_\$2/', -1, activities);

activities=prxchange('s/(,)([0-9])/\$1_\$2/', -1, activities);

activities=prxchange('s/([0-9])(,)/\$1_\$2/', -1, activities);

activities=prxchange('s/([A-Za-z])([0-9])/\$1_\$2/', -1, activities);

activities=prxchange('s/([A-Za-z])([A-Za-z])/\$1_1_\$2/', -1, activities);

activities=prxchange('s/(,)([A-Za-z])/\$1_1_\$2/', -1, activities);

array var(50) \$35.;                    - how to avoid hardcoding 50 in array statement???

i=1;

do while(scan(activities,i,'_') ne '');

var(i)=scan(activities,i,'_');

i+1;

end;

do i=1 to dim(var) by 2;

var(i)=(var(i)*15)/60;

end;

do i=2 to dim(var) by 2;

if upcase(var(i))="U" then VAR(i)="UNDER 2";

ELSE IF UPCASE(VAR(i))="O" THEN VAR(i)="OVER 2";

ELSE IF UPCASE(VAR(i))="C" THEN VAR(i)="CONTACT";

ELSE IF UPCASE(VAR(i))="N" THEN VAR(i)="NON-CONTACT";

ELSE IF UPCASE(VAR(i))="B" THEN VAR(i)="BREAK";

ELSE IF UPCASE(VAR(i))="L" THEN VAR(i)="LUNCH";

else if  UPCASE(VAR(i))="," THEN VAR(i)="NO ACTIVITY";

END;

End;

run;

I'm working on the above code to split string into variables.

String

28,16OB4N7L8O4L

28,12O8B20O

Output:

var1     Var2                    var3     var4           var5      var6                var7          var8                         var9     var10          var11     var12          var13          var14

7    NO ACTIVITY           4     OVER 2         0.25          BREAK        1          NON-CONTACT          1.75         Lunch        2         OVER 2        1             Lunch

7 NO ACTIVITY             3     OVER 2        2      BREAK                5             OVER 2

Is there any way to summarize data and transpose

Desired output :

NO_ACTIVITY     OVER_2     Break     Non_contact     Lunch    under_2      Contact

7                              6            0.25          1                    2.75          .               .

7                              3               2            .                    .                .               .

Any ideas on how to do this??

regards

Super User
Posts: 13,507

## Re: Summarizing and transposing data

I can't tell from your example what summary you want.

Contributor
Posts: 34

## Re: Summarizing and transposing data

I have a string  28,16OB4N7L8O4L using two arrays I had split into separate variables.

hrs1 hrs2 hrs3     hrs4     hrs5      hrs6     hrs7

28     16     1          4         7         8          4

cd1  cd2   cd3    cd4      cd5          cd6        cd7

,       O     B        N       L              O          L

Now I want to summarize across variables, if same value repeats in character variable in the above example 'O' AND 'L' ARE REPEATED  in that case I want to merge as one and add the respective hrs.

output should be:

cd1      cd2     cd3     cd4     cd5

,          O          B     N          L

hrs1     hrs2     hrs3     hrs4     hrs5

28          24     1          4           11(hrs5+hrs7)

(hrs2+hrs6)

hope you understand. thank you

Discussion stats
• 2 replies
• 286 views
• 0 likes
• 2 in conversation