## proc sql instead of proc transpose

Solved
Super Contributor
Posts: 301

# proc sql instead of proc transpose

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.

Accepted Solutions
Solution
‎06-02-2012 06:51 PM
Posts: 3,167

## Re: proc sql instead of proc transpose

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

All Replies
Solution
‎06-02-2012 06:51 PM
Posts: 3,167

## Re: proc sql instead of proc transpose

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

🔒 This topic is solved and locked.