SAS Proc Tabulate - Adding Row Titles which are not part of dataset

Question

I am working with Claims data in which we have four different encounter types: Dental, Institutional, Professional and Pharmacy. I need to develop separate reports for different organizations in SAS Proc Tabulate which show claims by these four types. Some organizations have all four types and others have only 3. But if a plan does not have one particular type, I still have to show it with missing values and a header in the report.

Say Plan A has only Institutional, Pharmacy and Professional types and no Dental claims. I want to show all the four types listed out with missing values for Dental. Can this be achieved with a Proc tabulate step? (Or will I have to modify the data set to put null values for each type?) Any help will be much appreciated.

This is the main code:

proc tabulate data=servmnth format=COMMA.0;
class plan_alias encounter_type service_Month;
var netted_claim_count;
format encounter_type $enc.;
table encounter_type= ' ' ALL='Total', sum=' '*netted_claim_count= 'Netted 
Claims by Service Month'*service_Month = ' ';
where plan_alias="&plan.";
run;

It was all good until I realized some plans were straight up missing one or two encounter types. I tried putting a format for encounter type using proc format but that did not work. I am wondering if there is anything I can attach to encounter_type variable in the table statement which would show 4 rows no matter what.

Have you already tried something on your own? If so please show your code to help us helping you! — Marvin Fischer
Have you tried using the classdata option in proc tabulate? This might help. — user667489
What do you need to report for the case of a plan having no claims ? — Richard

Richard Richard · Accepted Answer · 2018-02-20T19:06:01

The classdata= of the tabulate should be an explicit crossing of all the dimensional values you want to appear in the output. Class data can be externally enforced from lists of allowed values, or generated from the data= prior to the tabulate.

Consider some sample claim count data, with the special property that for each plan the encounter_types are restricted to a distinct set of values.

data claim_counts;
  do claim_id = 1 to 1e5;
    plan_id = floor(16 * ranuni(123));
    if plan_id = 0 then continue;
    date = mdy(ceil(12*ranuni(123)),1,2017);
    claim_count = ceil(abs(12*rannor(123)));
    member_id = 1e8 +  plan_id * 1e6 + floor(1e6 * ranuni(123));
    do until (band(plan_id,2**(encounter_type-1)));
      encounter_type = ceil(4*ranuni(123));
    end;
    output;
  end;
  format date yymmd.;
run;

The class data can be constructed from different lists of allowed values

* preordained, external class data (defined dimension data);

data plans;
  do plan_id = 0 to 15;
    output;
  end;
run;

data encounter_types;
  do encounter_type = 1 to 4;
    output;
  end;
run;

data months;
  do month = 1 to 12;
    date = mdy(month,1,2017);
  end;
  format date yymmd.;
run;

* cross all allowed dimensional values for defined coverage;
 proc sql;
   create table classdata as
   select plan_id, encounter_type, date
   from plans, encounter_types, months;
quit;

Or constructed from the dimensional coverage observed in the data

proc sql;
  create table classdata2 as
  select plan_id, encounter_type, date
  from (select distinct plan_id from claim_counts)
     , (select distinct encounter_type from claim_counts)
     , (select distinct date from claim_counts)
  ;

In large data sets the observed coverage is highly likely to match the defined coverage

Finally, use the classdata= option in the tabulate statement. In the following I also added plan_id to the table in the page dimension

proc tabulate data=claim_counts classdata=classdata;
  class plan_id encounter_type date;
  var claim_count;

  table 
    plan_id
    ,
    encounter_type = 'type' ALL
    ,
    sum = ' ' * claim_count*f=comma9. * date = ' '
    ;
  ;
run;

The page dimension will allow you to create output for more than one plan_id at a time and it will identify the plan for which the table below belongs to. Plan identification could also by done using a by statement or a title statements.

SAS Proc Tabulate - Adding Row Titles which are not part of dataset

1 Answers