BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BTAinRVA
Quartz | Level 8

I am just learning to use Tabulate and I have a table of college student enrollments and have created a table that calculates the number of enrollments in each curriculum for each of the past 5 academic years using code similar to this:

Proc Tabulate data=test;

    class curr acadyear;

    tables curr all, acadyear*(N all);

run;

and it gives me a table like this:

Curr20200708200809200910201011201112

101

201
301

What I want to add is another column that calculates the mean for each curriculum for the 5 years. I've tried several things (most of which gave me the error message associated with not having an analysis variable). Is there a way to trick Tabulate to give me the mean of a class variable?

Thanks,

Brian

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Brian,

Your current data set will not support the table you want.  But you're not that far from it.  Try this preparatory step:

proc freq data=test;

   tables curr * acadyear / noprint out=summarized (keep=curr acadyear count);

run;

Then you should be able to use the data set SUMMARIZED as the input to PROC TABULATE.  Include a VAR COUNT; statement, and ask for the MEAN statistic instead of the N statistic:

proc tabulate data=summarized;

   class curr acadyear;

   var count;

   tables curr all, (acadyear all) * count * mean;

run;

That's untested code, but it should be pretty close to what you need.

Good luck.

View solution in original post

9 REPLIES 9
Reeza
Super User

You don't have a var statement specifying what variable to summarize. And when you say 'MEAN' do you mean the average number of enrollments over the 5 years? Or something else?

BTAinRVA
Quartz | Level 8

Reeza,

Thanks for the response!

Yes, I want the average enrollments over the 5 year period. This seems like it should be very simple to do. I'm not sure there is a variable I can specify in a Va statement. The number of enrollments is based on the class variable acadyear. Can I somehow create a new variable that I could calculate the 5-year average for each curriculum?

Cynthia_sas
SAS Super FREQ

Hi:

  I guess I don't understand what you mean, when you say that the "number of enrollments is based on the class variable ACADYEAR" -- don't you have a separate variable for enrollments? What is the structure of your input data?

  I don't understand how that TABLE statement is giving you that output? Where is the BOX area that TABULATE puts at the intersection of the row headers and the column headers? Where is your extra column underneath every ACADYEAR value for the ALL?

  So, here's some code to try. Look at the structure of SASHELP.SHOES. Where I have REGION, you have CURR. Where I have PRODUCT, you have ACADYEAR. Report #1 shows what you should be getting based on the code you posted. Report #2 is what I think you want IF you have a separate ENROLLMENT variable that you can put in a VAR statement (such as where I have SALES).

cynthia

ods listing close;

ods html file='c:\temp\show_mean.html';

Proc Tabulate data=sashelp.shoes;

  where region in ('Asia', 'Canada', 'Pacific');

  title '1) You should be getting this';

    class region product;

    tables region all,

           product*(N all);

run;

    

Proc Tabulate data=sashelp.shoes;

  where region in ('Asia', 'Canada', 'Pacific');

  title '2) What I think you want';

    class region product;

    var sales;

    tables region=' ' all,

           product*(N=' ') all='Tot Cnt'*n=' ' sales='Avg Sales'*mean=' ' / box=region;

run;

ods html close;

BTAinRVA
Quartz | Level 8

Cynthia,

Thanks very much for responding!

Unfortunately I am very dense when it comes to Proc Tabulate. Here's the exact code I'm using, which works great to give me a table with curriculum and acadplan going down the first two columns and then the next 5 columns contain enrollments for the academic years from 2007 to 2012. What I want is a last column that calculates the 5-year average enrollment. But my table doesn't really have an analysis variable to put into a Var statement. It seems I need some type of derived variable but I'm having trouble figuring out what.

proc tabulate data = enr.AnnFiveYearCombined;

      class CURR ay acadplan;

      table CURR=''*(acadplan='' all='Subtotal') all='Total', ay=' '*N='';

      title 'Enrollments for Programs 2007-08 through 2011-12';

run;

Cynthia_sas
SAS Super FREQ

Hi:

  No worries, it's just that this recently posted TABULATE code is significantly different from what you originally posted and with TABULATE, that makes a difference to understanding what the results will look like.

  So, AY cannot be a YEAR value and an ENROLLMENT value. Unless you mean that the TOTAL COUNT of all the AY values divided by 5 is what you want to see in your final column. This is where seeing some of your data would be helpful.

  Here's what AY*N does inside TABULATE. Every unique value of AY will get a column header. Then the number in the cell will be the count of observations for the intersection of the CURR*ACADPLAN with that value of AY. The N in the cell is the statistic that SAS calculates based on the number of obs that TABULATE would place in that cell. Once TABULATE plops a number in a cell coming from the N statistic, it doesn't "remember" what that number was when it moves onto the next column or cell. When you use ALL, TABULATE "recounts" all the obs that fall into that category.

  To really help you, the structure of your data is critical. What does enr.AnnFiveYearCombined look like??? I know that you might not be able to post the EXACT data, but can you make some data with FAKE values for CURR and ACADPLAN so that it is easier to make a recommendation on how to get what you want. Some data manipulation may be required. Hard to predict without seeing the structure of what you are starting with.

cynthia

BTAinRVA
Quartz | Level 8

Cynthia,

Thanks again for the reply!

Each record in the data table contains student information. The class variables Curr, AcadPlan, and ay are all non-numeric character data. There are a lot of other variables, most of which are non-numeric character data. The data table is like this:

Curr     AcadPlan     AY          StudentID

101     11               200708     1111111

101     21               200708     2222222   

201     11               200708     3333333

Hope this better explains what I'm working with.

Brian

Astounding
PROC Star

Brian,

Your current data set will not support the table you want.  But you're not that far from it.  Try this preparatory step:

proc freq data=test;

   tables curr * acadyear / noprint out=summarized (keep=curr acadyear count);

run;

Then you should be able to use the data set SUMMARIZED as the input to PROC TABULATE.  Include a VAR COUNT; statement, and ask for the MEAN statistic instead of the N statistic:

proc tabulate data=summarized;

   class curr acadyear;

   var count;

   tables curr all, (acadyear all) * count * mean;

run;

That's untested code, but it should be pretty close to what you need.

Good luck.

BTAinRVA
Quartz | Level 8

Astounding,

Thanks for the reply. I'll try that out in the morning!

BTAinRVA
Quartz | Level 8

Astounding,

That's the ticket!  Thanks,

Brian Adams

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 2341 views
  • 6 likes
  • 4 in conversation