Proc SQL Modifying Tables and using Proc Tabulate

[mreynaud]

Hello,

I have two tables, created using proc sql and I am attempting to combine them to a single table to ultimately create a crosstab table.

My first table is 'priority'

proc sql; create table Priority as
	select priority , 
	round(avg(number_of_business_days),0.1) "Average Number of Business Days"
	from work.'test'n
		where Request_type="Advance"
			group by priority;
quit;

My second table is 'complexity'

proc sql; create table Complexity as
	 select complexity, 
		round(avg(number_of_business_days),0.1) 'Average Number of Business Days' 
	from work.'test'n
		where Request_type="Advance"
			group by complexity;
quit;

Here I joined the tables, however my crosstab report does not seem to display all of the data

 proc sql; create table PriorityComplexity as
 	select *
	from work.priority, work.complexity;
quit; 

proc tabulate data= work.PriorityComplexity;
class complexity priority;
var _tema001;
table complexity Priority; 
run;

 Here is the result:

 

But I want something similar (doesn't have to be exact) to this report I made in excel:

[ballardw]

I suggest that you NAME your variables

proc sql; create table Priority as
	select priority , 
	round(avg(number_of_business_days),0.1) AS somevariablename "Average Number of Business Days"
	from work.'test'n
		where Request_type="Advance"
			group by priority;
quit;

otherwise it is quite possible that the calculated variable will have the same name in both the priority and complexity data sets. So when you join them you only get one.

Then you don't even use the Var variable in this:

proc tabulate data= work.PriorityComplexity;
class complexity priority;
var _tema001;
table complexity Priority; 
run;

But if I understand what you are attempting there is likely no reason for any of that sql.

I suggest that you try this as a start.

proc tabulate data=work.test;
   class complexity priority;
   var  number_of_business_days;

   table (all='All complexity' complexity),
         (all='All Priorities' Priority)*number_of_business_days*mean=' '
         /misstext=' '
   ;
run;

If you have missing values of either complexity or priority then it might be a good idea to provide example data.

You do not need to use work.'test'n as work.test is a normal SAS data set name. The name literal 'something odd'n is needed when you have any character other than letter or underscore start the name and any character other than letter, underscore or digit in the remainder.

When you use Proc tabulate the Table statement uses a comma to delimit rows, columns and pages. So your original tabulate code does not "cross" anything as you would need a comma between the dimensions. So when there is only one provided dimension in the table statement it is the "column" dimension.

To have a statistic within the level of a class variable you use classvar*varvariable*(<statistics go here>). Only place statistics in one dimension as Tabulate crosses everything and you can't have a column "mean" of a row "count" variable or similar. The All uses all of the records. Parentheses group things. So (All  ClassVariable)* variable*(<statistics>) is a shorter way of doing

All * variable*(<statistics>)  ClassVariable*variable*(<statistics>).

The variablename=' ' suppresses placing the name of the variable in the table, or <statistic name>=' ' suppresses the default name of the statistic. You can place other text inside the ' ' if you want something provided. You can use *f= to provide a display format. Default Tabulate will display 2 decimals for most statistics. Use mean=' '*f6.1 for example to show 1 decimal.

By default SAS formats will round using the typical rules for rounding.

If this doesn't perform as expected : Provide example data in the form of data step code for the variables needed, at least 2 levels of each of the classification variables an probably 3 or 4 values of the number of business days and what the worked example for that provided data should look like.

Tabulate will exclude records with any missing values for class variables. So if you have such you need to provide a worked example with that as well.