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!
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
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;
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.
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.
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;
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;
It is hard for proc tabulate , try proc report .
totaly confused, Please detail your problem once again if you may.
thanks
puneet
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.