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?
@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;
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;
@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?
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.
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 |
@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;
Awesome! That does work. Can you explain what you did?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.