Merging two proc tabulate tables

[zouinenoah]

I have two proc tabulate tables shown in the photo below. I was wondering if there is a way to combine the tables together to look more like the excel table photo also attached? The "Measure", "Definition", the all black column, and the target column are not needed they just are in the excel file.

The top table is the "(Hospitalists)" and the bottom table is the "(All Providers)".

[ballardw]

Please show the code used to create the Proc Tabulate tables.

You would likely need to have a VARIABLE in a data set that indicates "hospitalist" or "all providers". Is the resulting table supposed to only be two rows or are you wanting the individual values, such as 913, 914 etch that appears under your "Aurora Hospital" heading?

And the Target value as a variable somewhere if you expect Tabulate to create it.

[zouinenoah]

For my code:

data aurora_1m_hosplst;
set ce.MartCEInpatientEncounter;
keep CompanyCode Site MRN PTLastName PTFirstName AdmitDate AdmitFullDate DischargeDate DischargeFullDate DischargeYr;

* limit to months of interest;
if mdy(01,01,2021)<=datepart(DischargeFullDate)<=mdy(07,31,2021);	* update monthly;

* hospitalist attendings only and Discharge time at or before noon;
where AttendingSpecialty='Hospitalist' AND timepart(DischargeFullDate)<='12:00't;

* only aurora hospitals;
if "900"<=CompanyCode<="916";

* new hospital names variable;
if CompanyCode="900" then hosp_n="Aurora Medical Center Grafton";
if CompanyCode="902" then hosp_n="Aurora St. Luke's Medical Center";
if CompanyCode="903" then hosp_n="Aurora St. Luke's South Shore";
if CompanyCode="904" then hosp_n="Aurora Sinai Medical Center";
if CompanyCode="905" then hosp_n="Aurora West Allis Memorial";
if CompanyCode="906" then hosp_n="Aurora Medical Center Washington County";
if CompanyCode="907" then hosp_n="Aurora Memorial Hospital of Burlington";
if CompanyCode="908" then hosp_n="Aurora Medical Center Kenosha";
if CompanyCode="909" then hosp_n="Aurora Lakeland Medical Center";
if CompanyCode="910" then hosp_n="Aurora Sheboygan Memorial Medical Center";
if CompanyCode="911" then hosp_n="Aurora BayCare Medical Center"; 
if CompanyCode="912" then hosp_n="Aurora Medical Center Oshkosh"; 
if CompanyCode="913" then hosp_n="Aurora Medical Center Summit";
if CompanyCode="914" then hosp_n="Aurora Medical Center Manitowoc County"; 
if CompanyCode="916" then hosp_n="Aurora Bay Area";

* Sorting Discharge Dates by month;
proc sort data=aurora_1m_hosplst out=lm_hosplst_sort;
	by DischargeFullDate;
run;

*Finding percent of people at each Hospital discharged by noon for every month;
proc tabulate data=lm_hosplst_sort;
	class CompanyCode /preloadfmt order=data;
	format CompanyCode;

 	class DischargeFullDate;
	format DischargeFullDate dtmonyy7.;

	table CompanyCode='Aurora Hospital',
		  DischargeFullDate*rowpctn /printmiss;
	title '% Inpt Discharges Before Noon';
	title2 '(Hospitalists)';
run;


*****************  Now repeating the process for All Providers *****************;

data aurora_lm_allProviders;
set ce.MartCEInpatientEncounter;
keep CompanyCode Site MRN PTLastName PTFirstName AdmitDate AdmitFullDate DischargeDate DischargeFullDate DischargeYr;

if mdy(01,01,2021)<=datepart(DischargeFullDate)<=mdy(07,31,2021);	* update monthly;

* Discharge time at or before noon;
where timepart(DischargeFullDate)<='12:00't;

* only aurora hospitals;
if "900"<=CompanyCode<="916";

* new hospital names variable;
if CompanyCode="900" then hosp_n="Aurora Medical Center Grafton";
if CompanyCode="902" then hosp_n="Aurora St. Luke's Medical Center";
if CompanyCode="903" then hosp_n="Aurora St. Luke's South Shore";
if CompanyCode="904" then hosp_n="Aurora Sinai Medical Center";
if CompanyCode="905" then hosp_n="Aurora West Allis Memorial";
if CompanyCode="906" then hosp_n="Aurora Medical Center Washington County";
if CompanyCode="907" then hosp_n="Aurora Memorial Hospital of Burlington";
if CompanyCode="908" then hosp_n="Aurora Medical Center Kenosha";
if CompanyCode="909" then hosp_n="Aurora Lakeland Medical Center";
if CompanyCode="910" then hosp_n="Aurora Sheboygan Memorial Medical Center";
if CompanyCode="911" then hosp_n="Aurora BayCare Medical Center"; 
if CompanyCode="912" then hosp_n="Aurora Medical Center Oshkosh"; 
if CompanyCode="913" then hosp_n="Aurora Medical Center Summit";
if CompanyCode="914" then hosp_n="Aurora Medical Center Manitowoc County"; 
if CompanyCode="916" then hosp_n="Aurora Bay Area";

proc sort data=aurora_lm_allProviders out=lm_allProviders_sort;
	by DischargeFullDate;
run;

proc tabulate data=lm_allProviders_sort;
	class CompanyCode /preloadfmt order=data;
	format CompanyCode;
 	class DischargeFullDate;
	format DischargeFullDate dtmonyy7.;
	table CompanyCode='Aurora Hospital',
		  DischargeFullDate*rowpctn /printmiss;

	title '% Inpt Discharges Before Noon';
	title2 '(All Providers)';
run;

[ballardw]

You haven't answered the question about the "in Excel" being two rows or all the company codes.

What are all the values for the AttendingSpecialty variable?

I think this can be done with a format for that variable but would need some example data (and keep it)

Actually I don't think your two data sets are needed if you use formats carefully.

[zouinenoah]

I'm breaking it down by Company Code then each company code has the just "Hospitalists" group and the "all providers" group (which includes Hospitalists) for each Company Code. Just exactly as the excel photo shows. There are more than just those two hospital codes like the code shows it goes to 916.

[ballardw]

---

@zouinenoah wrote:

I'm breaking it down by Company Code then each company code has the just "Hospitalists" group and the "all providers" group (which includes Hospitalists) for each Company Code. Just exactly as the excel photo shows. There are more than just those two hospital codes like the code shows it goes to 916.

---

Please reread that sentence. You "picture" of Excel shows exactly two rows. Which is why I asked for clarification.

Not the report groups. What are the actual VALUES possible for your AttendingSpecialty variable. One strongly suspects that there are more than 2 "specialties". Otherwise they aren't very special.

I ask because Proc Tabulate is one of the Procedures that will use Multilabel formats and that would be one way, possibly the easiest. A brief example using the SASHELP.CLASS data set that you should have to test code.

Proc format;
value $classname (multilabel notsorted)
'John','Mary',"Thomas"="Group1"

"Alfred","Alice","Barbara","Carol","Henry","James","Jane","Janet","Jeffrey","John","Joyce","Judy",
"Louise","Mary","Philip","Robert","Ronald","Thomas","William"
='All students'
;

run;

proc tabulate data=sashelp.class;
  class name/mlf preloadfmt order=data;
  format name $classname.;
  var height;
  table name='Report group',
        height*(n min mean max)
   ;
run;

The Proc format creates a multilabel format that, for the few procedures that can use them, will do a summary with the students grouped into two report groups, "group 1" which would correspond to your "hospitalists" and the all of the students. You can look at the data set and verify there are only 19 records pretty easily.

The values of your actual AttendingSpecialty can be used to create the two levels of your report. However the way multilabel formats works requires an explicit list of values to create. Which is why I asked.

With this, and a similar format for you companycode there is 1) no need for either of the temporary data set you create for the report, 2) no need to add additional variables and 3) only have to set the limits in one place.

Also having a format for the CompanyCode values (or possibly 2 or 3 depending on the types of reports you do) means that if you have to add a code you don't have to change a bunch of code to add an additional "if code='blah' then text='blah blah blah'.

I write reports based on clinics and have multiple format depending on whether I want to 1) include the region identifier in the clinic text, 2) just show the region, 3) show a long or short version of the text, 4) multilabel to get a region summary along with the clinics. With the "region" format I can select in a WHERE statement which clinics to use with something like:

where put(clinicid, region.) in ('North' 'South');

The above would select all of the clinics associated with the North or South Region. The multilabel format associated with clinic creates a summary line for the entire region as well as the clinics.

And this just scratches the surface of how formats, while a little work up front, may save a lot of work later.

Conceptually your  problem can be reduced to:

proc tabulate data=ce.MartCEInpatientEncounter;
   where ( mdy(01,01,2021)<=datepart(DischargeFullDate)<=mdy(07,31,2021) )
   and  timepart(DischargeFullDate)<='12:00't
   and  "900"<=CompanyCode<="916";
	class CompanyCode /preloadfmt order=data;
	format CompanyCode $companycode.;
   class AttendingSpecialty/mlf;
   format AttendingSpecialty $AttendingSpecialty.;
 	class DischargeFullDate;
	format DischargeFullDate dtmonyy7.;
	table AttendingSpecialty*CompanyCode='Aurora Hospital',
		  DischargeFullDate*rowpctn /printmiss;

run;

once the companycode and AttendingSpecialty formats are created.

For your example here's the companycode format:

proc format;
value $CompanyCode;
"900" ="Aurora Medical Center Grafton"
"902" ="Aurora St. Luke's Medical Center"
"903" ="Aurora St. Luke's South Shore"
"904" ="Aurora Sinai Medical Center"
"905" ="Aurora West Allis Memorial"
"906" ="Aurora Medical Center Washington County"
"907" ="Aurora Memorial Hospital of Burlington"
"908" ="Aurora Medical Center Kenosha"
"909" ="Aurora Lakeland Medical Center"
"910" ="Aurora Sheboygan Memorial Medical Center"
"911" ="Aurora BayCare Medical Center" 
"912" ="Aurora Medical Center Oshkosh" 
"913" ="Aurora Medical Center Summit"
"914" ="Aurora Medical Center Manitowoc County" 
"916" ="Aurora Bay Area"
;
run;

which took less than two minutes to create from your code.

[zouinenoah]

proc format;
value $CompanyCode (multilabel notsorted);
"900" ="Aurora Medical Center Grafton"
"902" ="Aurora St. Luke's Medical Center"
"903" ="Aurora St. Luke's South Shore"
"904" ="Aurora Sinai Medical Center"
"905" ="Aurora West Allis Memorial"
"906" ="Aurora Medical Center Washington County"
"907" ="Aurora Memorial Hospital of Burlington"
"908" ="Aurora Medical Center Kenosha"
"909" ="Aurora Lakeland Medical Center"
"910" ="Aurora Sheboygan Memorial Medical Center"
"911" ="Aurora BayCare Medical Center" 
"912" ="Aurora Medical Center Oshkosh" 
"913" ="Aurora Medical Center Summit"
"914" ="Aurora Medical Center Manitowoc County" 
"916" ="Aurora Bay Area"
;
value $AttendingSpecialty;
"Hospitalist"='Hospitalist'

" ", "Addiction Medicine", "Advanced Heart Failure and Transplant Cardiology", "Anesthesiology", "Bariatric Surgery", "Cardiac Electrophysiology",
"Cardiology", "Cardiothoracic Anesthesiology", "Cardiothoracic Surgery", "Cardiovascular & Thoracic Surgery", "Cardiovascular Disease", "Child Psychiatry",
"Child/Adolescent Psychiatry", "Colorectal Surgery", "Critical Care", "Critical Care Medicine", "Electrophysiology", "Emergency Medicine", "Family Medicine",
"Family Practice","Gastroenterology","General & Vascular Surgery","General Medicine","General Surgery","General Surgey","Geriatric Medicine","Gynecologic Oncology",
"Hematology & Oncology","Hematology & Oncology","Hematology/Oncology","Internal Medicine","Interventional Cardiology","Maternal/Fetal Medicine","Neonatal & Perinatal Medicine",
"Neonatalogy","Neonatology","Neurological Surgery","Neurology","Neuroradiology","Neurosurgery","OB/GYN","Obstetrics","Obstetrics & Gynecology","Obstetrics/Gynecology",
"Oncology","Orthopaedic Surgery","Orthopedic Surgery","Orthopedics","Otolaryngology","Otorhinolaryngology","Pediatric","Pediatric Critical Care","Pediatric Emergency Medicine",
"Pediatric Gastroenterology","Pediatric General Surgery","Pediatric Hematology/Oncology","Pediatric Medicine","Pediatric Orthopedic Surgery","Pediatric Palliative Medicine",
"Pediatric Pulmonology","Pediatric Urology","Pediatrics","Physical Medicine & Rehab","Physical Medicine & Rehabilitation","Plastic Surgery","Plastic and Reconstructive Surgery",
"Plastic/Reconstructive Surgery","Podiatry","Psychiatry","Pulmonary Medicine","Radiation Oncology","Sleep Medicine","Surgery","Surgical Critical Care","Thoracic & CV Surgery",
"Thoracic Surgery","Thoracic and Cardiac Surgery","Trauma Surgery","Urogynecology","Urology","Vascular","Vascular & Interventional Radiology","Vascular Surgery","eICU physician",
"eICU-Lead"
='All Specialties';

run;

My mistake for lack thereof understanding. I have taken your advice, but when I write my proc format statement above I get the following errors: