topic Re: proc sql summary table considering distints subjids in SAS Procedures

proc sql summary table considering distints subjids

michtka — Fri, 15 Jun 2012 10:32:12 GMT

Hi guys, using the sql code below suggested by Hai.Kuo, I obtained this table:

Final table:

race male female total

black 2 1 3

latin 2 1 3

oriental 1 0 1

but now , considering a new imput of data, there are a subject that is duplicate, and I would like using the same sql but considering only distints subjects, then latin male has to be 1, instead of 2, and total has to be 2, then the table I want is:

race male female total

black 2 1 3

latin 1 1 2

oriental 1 0 1

Can you help me with this? Thanks. V.

****************************************************

*New input with a duplicate redord (subno=3);

data have;

length subno 4 race $10 sex $10;

input subno race sex ;

datalines;

1 black male

2 black female

3 latin male

4 black male

5 latin female

6 oriental male

;

run;

*code sugggested by code Hai.Kuo for an old input:

proc sql;

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

from have

group by race;

quit;

Re: proc sql summary table considering distints subjids

Alpay — Fri, 15 Jun 2012 11:05:20 GMT

You will need to get distinct values of race, subno and sex in a subquery and then sum it up.

proc sql;

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

from (select distinct race, subno, sex from have)

group by race;

quit;

Re: proc sql summary table considering distints subjids

michtka — Fri, 15 Jun 2012 14:39:59 GMT

Hello alpay, thanks.

Please, could you explain the the role of distinct in that subquery?...i.g...which variable is affected for this...race?

I was thinking of something like:

from (select distinct subno, race, sex)

instead of your line of code.

I will aprreciate you can explain to me the difference.

Thanks.

Re: proc sql summary table considering distints subjids

Alpay — Fri, 15 Jun 2012 16:45:33 GMT

It will get you the unique rows for variables specified in select clause. It is similar to 'group by' but no summary statistics computed.

select distinct race, subno, sex from have

the result of this query:

1 black male

2 black female

3 latin male

4 black male

5 latin female

6 oriental male

Using the result of this sub-query, you can get the counts by race.