topic Re: Using PROC Tabulate to include missing rows but not missing columns in SAS Programming

Using PROC Tabulate to include missing rows but not missing columns

mmaleta851 — Sat, 14 Jan 2023 00:39:14 GMT

Suppose I am running the following sales firm:

data work.table;
input Name:$24. Date:YYQ8. Trips Sales;
format Name $24. Date YYQ8.;
datalines;
John 2021Q1 10 100
John 2021Q3 2 200
John 2021Q4 4 400
David 2021Q1 16 300
David 2021Q2 8 600
David 2021Q4 19 100
Tim . 0 .
Karen 2021Q1 20 500
Jen . 0 .
;
run;

If a sales rep drove a trip that led to a sale at some point during 2021, they get credit for the trips and sales they drove during that quarter. If a rep did not drive a trip, the date and sales columns are null and trips are listed as 0. John, David, and Karen all drove trips in 2021 while Tim and Jen did not. Suppose I want to tabulate a report to see how many trips and sales were driven each quarter and what the contribution of each sales rep was. I want to include reps that did not drive any trips, so I run the following code:

proc tabulate data=work.table;
class Name Date / missing;
var Trips Sales;
tables Name All="Grand Total", (Date="" ALL="Grand Total")*(Trips*(sum=''*f=6.0) Sales*(sum=''*f=DOLLAR12.));
run;

The problem is that while I get missing values for Tim and Jen, I also get a missing value as a column as well:

Is there a way that I run proc tabulate with the missing values for Tim and Jen without the missing value column. Alternatively is there a way I can create an additional column to indicate that Tim and Jen have no date values?

Re: Using PROC Tabulate to include missing rows but not missing columns

Patrick — Sat, 14 Jan 2023 02:29:56 GMT

Does one of below two options return what you're after?

options missing=' ';
proc tabulate data=work.table;
  where not (missing(date) and missing(sales));
  class Name Date / missing;
  var Trips Sales;
  tables 
    Name All="Grand Total", 
    (Date="" ALL="Grand Total")*(Trips*(sum=''*f=6.0) Sales*(sum=''*f=DOLLAR12.));
run;

proc format;
  value date_cat
    . = 'missing date'
    other=[YYQ8.]
    ;
run;
options missing=' ';
proc tabulate data=work.table;
  format date date_cat.;
  class Name Date / missing;
  var Trips Sales;
  tables 
    Name All="Grand Total", 
    (Date="" ALL="Grand Total")*(Trips*(sum=''*f=6.0) Sales*(sum=''*f=DOLLAR12.));
run;

Re: Using PROC Tabulate to include missing rows but not missing columns

mmaleta851 — Sat, 14 Jan 2023 02:31:46 GMT

The second one but without the missing date column.

Re: Using PROC Tabulate to include missing rows but not missing columns

Patrick — Sat, 14 Jan 2023 02:47:00 GMT

@mmaleta851 wrote:
The second one but without the missing date column.

Then wouldn't that be the first one? If not then can you please copy/paste a screenshot of the 2nd report where you highlight what needs to be removed?

Re: Using PROC Tabulate to include missing rows but not missing columns

mmaleta851 — Sat, 14 Jan 2023 05:19:15 GMT

Hey Patrick, I want to get rid of the Missing data columns, but I want to keep the rows that have missing data to indicate which sales reps did not complete any sales in 2021.

Re: Using PROC Tabulate to include missing rows but not missing columns

mmaleta851 — Sat, 14 Jan 2023 05:38:29 GMT

Basically I want something like this:

	2021Q1		2021Q2		2021Q3		2021Q4		Grand Total
	Trips	Sales	Trips	Sales	Trips	Sales	Trips	Sales	Trips	Sales
Name
David	16	$300	8	$600			19	$100	43	$1,000
Jen									0	$0
John	10	$100			2	$200	4	$400	16	$700
Karen	20	$500							20	$500
Tim									0	$0
Total	46	$900	8	$600	2	$200	23	$500	79	$2,200

Re: Using PROC Tabulate to include missing rows but not missing columns

Patrick — Sat, 14 Jan 2023 10:50:33 GMT

@mmaleta851 wrote:

Hey Patrick, I want to get rid of the Missing data columns, but I want to keep the rows that have missing data to indicate which sales reps did not complete any sales in 2021.

Then below should work.

proc sql;
  create table work._classdata as
  select distinct
    name,
    max(date) as date format=YYQ8.
  from work.table
  ;
quit;

options missing=' ';
proc tabulate data=work.table classdata=work._classdata;
  where not (missing(date) and missing(sales));
  class Name Date;
  var Trips Sales;
  tables 
    Name All="Grand Total", 
    (Date="" ALL="Grand Total")*(Trips*(sum=''*f=6.0) Sales*(sum=''*f=DOLLAR12.));
run;

Re: Using PROC Tabulate to include missing rows but not missing columns

mmaleta851 — Mon, 16 Jan 2023 18:49:27 GMT

Awesome! That does work. Can you explain what you did?

Re: Using PROC Tabulate to include missing rows but not missing columns

Patrick — Tue, 17 Jan 2023 00:57:16 GMT

Secondary table _classdata contains all combinations of categories that you wanted in your report.

The where clause subsets the primary table to only the rows you actually want to include in your report.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1hpbwr9acrbmen1e2d6hxjkotm1.htm

Example 2: Specifying Class Variable Combinations to Appear in a Table

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1hpbwr9acrbmen1e2d6hxjkotm1.htm#n1k7cffoxmhkexn1k3e4wc7yu6s9