SAS - Split single column into two based on value of an ID column

Question

I have data which is as follows.

data have;
  input group replicate $ sex $ count;

datalines;
1 A F 3
1 A M 2
1 B F 4
1 B M 2
1 C F 4
1 C M 5
2 A F 5
2 A M 4
2 B F 6
2 B M 3
2 C F 2
2 C M 2
3 A F 5
3 A M 1
3 B F 3
3 B M 4
3 C F 3
3 C M 1
;
run;

I want to break the count column into two separate columns based on gender.

                                                 count_    count_
                    Obs    group    replicate    female     male

                     1       1          A           3         2
                     2       1          B           4         2
                     3       1          C           4         5
                     4       2          A           5         4
                     5       2          B           6         3
                     6       2          C           2         2
                     7       3          A           5         1
                     8       3          B           3         4
                     9       3          C           3         1

This can be done by first creating two separate data sets for each level of sex and then performing a merge.

data just_female;
  set have;
  where sex = 'F';
  rename count = count_female;
run;

data just_male;
  set have;
  where sex = 'M';
  rename count = count_male;
run;

data want;
  merge
    just_female
    just_male
  ;
  by
    group
    replicate
  ;

  keep
    group
    replicate
    count_female
    count_male
  ;
run;

Is there a less verbose way to do this which doesn't require the need to sort or explicitly drop/keep variables?

Answer 1

You can do this using proc transpose but you will need to sort the data. I believe this is what you're looking for though.

proc sort data=have;
by group replicate;
run;

The data is sorted so now you have your by-group for transposing.

proc transpose data=have out=want(drop=_name_) prefix=count_;
by group replicate;
id sex;
var count;
run;

proc print data=want;

Then you get:

Obs    group    replicate    count_F    count_M

 1       1          A           3          2
 2       1          B           4          2
 3       1          C           4          5
 4       2          A           5          4
 5       2          B           6          3
 6       2          C           2          2
 7       3          A           5          1
 8       3          B           3          4
 9       3          C           3          1