Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 8

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

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!

SAS Super FREQ
Posts: 8,745

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

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

Trusted Advisor
Posts: 1,204

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

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;

Occasional Contributor
Posts: 8

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

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.

Super User
Posts: 17,963

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

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.

Super User
Super User
Posts: 7,430

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

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;

Super User
Super User
Posts: 7,430

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

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;

Super User
Posts: 9,691

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

It is hard for proc  tabulate , try proc report .

Occasional Contributor
Posts: 5

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

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

thanks

puneet

Ask a Question
Discussion stats
  • 8 replies
  • 233 views
  • 0 likes
  • 7 in conversation