topic Re: how can I use proc tabulate to display only some of the responses but not all of them? in SAS Procedures

how can I use proc tabulate to display only some of the responses but not all of them?

jenny_li — Sun, 06 Jul 2014 13:42:05 GMT

Hi, I am a new SAS user so the question may be quite simple to you.

Below shows how my dataset looks like.

Class Name Subject Pass_or_not Gender

A John Maths Y M

A Mary Maths N F

B Amy Maths Y F

B Peter Maths Y M

B Sam Maths N M

A John English Y M

A Mary English Y F

B Amy English N F

B Peter English Y M

B Sam English N M

I used proc tabulate with simple code so that I will have the following table

Subject Gender

Class Maths English M F

Pass_or_not Pass_or_not

Y N Y N

A 1 1 2 0 1 1

B 2 1 1 2 2 1

What if I only want tot display the result of "Y" in Pass_or_not but not including "N"?

That's what I want.

Subject Gender

Class Maths English M F

Pass_or_not Pass_or_not

Y Y

A 1 2 1 1

B 2 1 2 1

I only know that I can't use where statement as this will minimize my data base.

It would be very appreciate if anyone can help!

Re: how can I use proc tabulate to display only some of the responses but not all of them?

Cynthia_sas — Sun, 06 Jul 2014 15:34:23 GMT

Hi:

Just curious...why is your gender for class A only 1 male and 1 female. How are you counting for GENDER? You have 10 observations -- are the names unique? Is the John for class A Maths the same as the John for class A English? Same question with Amy, Peter & Sam. In Subject*Pass_or_not, it seems that you only want to see the YES, but for Gender, you want to see the count of unique occurences of name for each class? Is that correct? You may need to pre-process your data and get the overall counts for the Y values and then do a final print of the output.

What TABULATE code have you tried?

cynthia

Re: how can I use proc tabulate to display only some of the responses but not all of them?

stat_sas — Sun, 06 Jul 2014 15:40:37 GMT

Try this.

proc tabulate data=have (where=(pass_or_not='Y'));

class class subject pass_or_not gender;

table class,subject*pass_or_not*N=' ' gender*N=' ';

run;

Re: how can I use proc tabulate to display only some of the responses but not all of them?

jenny_li — Sun, 06 Jul 2014 15:46:53 GMT

I'm sorry that I have used a wrong example in this case, my data does not look exactly like this, I just want to bring out the message that I have another field in the table which requires the whole dataset as a base so I can't use where statement. So actually you can replace the name of student for English subject to other names differ from that of Maths and the count of gender should be based on that 10 students. But your question is also an interesting question if my data really look like that, I would like to know the solution as well. But my main concern here is how to output the "Y" response only. Of course if someone can answer your question it would be great as actually I don't know the answer too.:smileysilly:

So maybe I will modify the example as below

Class Name marks Gender

A John 100 M

A Mary 70 F

B Amy 80 F

B Peter 90 M

B Sam 85 M

And I want to produce the below table, without the column of "M" for gender. I can't use where statement or otherwise the sum of marks would be based on "F" population only but not the whole class.

Gender Sum of marks

Class F

A 1 170

B 1 175

That's what I really want to ask, sorry for the misleading.

Re: how can I use proc tabulate to display only some of the responses but not all of them?

Reeza — Sun, 06 Jul 2014 17:08:06 GMT

I don't think you can right off the bat, you'll need to either modify the input data or the output data.

For modifying the input, perhaps add 2 columns Gender_M Gender_F that are 0/1 indicators and then sum them via proc tabulate.

Re: how can I use proc tabulate to display only some of the responses but not all of them?

PuneetSingh — Mon, 07 Jul 2014 09:44:34 GMT

totaly confused, Please detail your problem once again if you may.

thanks

puneet

Re: how can I use proc tabulate to display only some of the responses but not all of them?

RW9 — Mon, 07 Jul 2014 10:12:15 GMT

Why do you need to use proc tabulate for that?

proc sql;

create table WANT as

select distinct

A.CLASS,

A.FEMALE_COUNT,

B.SUM_OF_MARKS

from ( select CLASS,

COUNT(*) as FEMALE_COUNT

from HAVE

having GENDER="F") A

left join (select CLASS,

SUM(MARK) as as SUM_OF_MARKS

from HAVE);

on A.CLASS=B.CLASS;

quit;

Re: how can I use proc tabulate to display only some of the responses but not all of them?

RW9 — Mon, 07 Jul 2014 10:23:29 GMT

Tidied up a bit:

data have;
set sashelp.class;
if _N_=1 then do; score=12; class="A"; end;
if _N_=2 then do; score=20; class="A"; end;
if _N_=3 then do; score=3; class="A"; end;
if _N_=4 then do; score=45; class="A"; end;
if _N_=5 then do; score=5; class="B"; end;
if _N_=6 then do; score=7; class="B"; end;
if _n_ < 6 then output;
run;

proc sql;
create table WANT as
select distinct
          CORE.CLASS,
          ( select count(distinct THIS.NAME)
            from WORK.HAVE THIS
            where THIS.CLASS=CORE.CLASS and THIS.SEX="F") as FEMALE_COUNT,
          ( select sum(score)
            from WORK.HAVE THIS
            where THIS.CLASS=CORE.CLASS) as SUM_RESULTS
from    WORK.HAVE CORE;
quit;

Re: how can I use proc tabulate to display only some of the responses but not all of them?

Ksharp — Mon, 07 Jul 2014 13:24:18 GMT

It is hard for proc tabulate , try proc report .