BookmarkSubscribeRSS Feed
jenny_li
Calcite | Level 5

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!

8 REPLIES 8
Cynthia_sas
SAS Super FREQ

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

stat_sas
Ammonite | Level 13

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;

jenny_li
Calcite | Level 5

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.

Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

It is hard for proc  tabulate , try proc report .

PuneetSingh
Calcite | Level 5

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

thanks

puneet

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1555 views
  • 0 likes
  • 7 in conversation