proc sql instead of proc transpose

[michtka]

Hi Guys,

   using the code* below I obtain this:

Obs

race

sex

count

	1	black	female	1
	2	black	male	2
	3	latin	female	1
	4	latin	male	2
	5	oriental	male	1

code*:

-------------------------------------------------------

data have;

length  subno 4 race $10 sex $10;

input subno race  sex ;

datalines;

1  black male

2  black female

3  latin male

4 latin male

5 black male

6 latin female

7 oriental male

;

run;

proc sql;

create table race as

  select race,  sex

                  ,count(distinct subno) as count

    from have

    group by race, sex;

quit;

proc print data=race; run;

------------------------------------------------------

Anyone can help me to get the final table below using proc sql?....note: (please I know how to get the final table using proc transpose, but I would like to know how to do it with proc sql), thanks.

Final table:

race            male   female     total

black              2      1             3

latin                2      1             3     

oriental           1       0             1

V.

[Haikuo · Accepted solution]

OK, proc sql it is:

proc sql;

  select race, sum(sex='male') as male, sum(sex='female') as female, count(race) as total

  from have

  group by race;

quit;

Haikuo