topic Re: Transposing Multiple Observation to single variable in SAS Programming

Transposing Multiple Observation to single variable

Loobylou — Sat, 08 Aug 2020 21:08:04 GMT

I'm a relatively new SAS user, and looking for help to re-organise/summarise my data.

I have data on the gender of each Director in a series of firms, in the format:

FIRM ID GENDER

1 M

1 F

2 M

2 F

I want to be able to summarize and transpose this data, so I have only one observation per firm, and a count of how many male and female directors. So the resulting format would look like:

FIRM ID MALE FEMALE

1 2 1

2 1 1

I'm wondering if I can use PROC TRANSPOSE for this, but can't figure out how to do this together with a count of male/female. Grateful for any guidance/suggestions!

Re: Transposing Multiple Observation to single variable

novinosrin — Sat, 08 Aug 2020 21:12:20 GMT


data have;
input FIRM_ID          GENDER $;
cards;
1                           M

1                           M

1                           F

2                           M

2                           F
;

proc sql;
create table want as
select FIRM_ID,sum(gender='M') as Male,sum(gender='F') as Female
from have
group by firm_id;
quit;

Re: Transposing Multiple Observation to single variable

Loobylou — Sat, 08 Aug 2020 21:30:55 GMT

This has worked like a dream - thank you!