PROC TABULATE preloadfmt question

[hr667]

Hello,

I am creating a report using data being collected over time using proc tabulate. The tables include frequencies by cluster separated into three tables by overall site. The tables for screening included all of the rows for clusters because there is data for all clusters, but as time moves on (the 6th month visit) not all of the clusters have data yet. So, in my report the rows for those clusters are missing. I would like to show all rows for clusters in all tables even if there is no data available. I did some searching online and I'm aware of the preloadfmt statement. When I use this it includes the clusters for the entire study in every table, not just the clusters for that specific site which is not what I want. Is there a way to load the formats by site so that only the clusters in a site show in that specific site's tables?

Thanks

[ballardw]

Data and code you are currently using may be helpful. And tell us which role in terms of this question uses which variable(s).

---

Is there a way to load the formats by site so that only the clusters in a site show in that specific site's tables?

---

No a variable has the same format assigned. Some things can be done with overrides but those basically related to STYLE overrides not the row/column heading values. But your description may mean that your code is applying preloadfmt to more than just the variable of interest.

You can have multiple Class, Classlev and Var statements in proc Tabulate to apply different options to different variables.

[hr667]

I cannot share the data but I will do my best to describe what I'm talking about. This is generally what my code looks like (without all of the style and labeling information).

proc tabulate data = table3 missing;

class site;

class cluster / preloadfmt order = data;

var v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11;

table site = ' ', cluster ALL = 'Total', v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11/ printmiss misstext = '0';

format cluster $clusterfmt.;

run;

For each proc tabulate I want three tables, one for each site. And I want all of the clusters in each site to be visible in the corresponding site's table even when no data is available.

[Cynthia_sas]

Hi:
For anyone to run your code, they have to make up some fake data AND they have to make up a format. What they choose to design as fake data may or may not match your scenario. It should be possible for you to generate some fake data for your example, by reducing the number of cluster values to 3 or 4 and the number or sites to 2 or 3 and just have like under 100 obs. Otherwise, without seeing some examples of how your data is structured and what your format looks like it is nearly impossible to do more than guess at what you want.
Cynthia

[hr667]

Here is a very basic example that demonstrates what I am talking about. When you run this, the tabulate procedure produces two tables for each table statement (one for each site) but includes all four clusters (a,b,c,d) when that is impossible. Clusters a & b correspond to site 1 and clusters c & d correspond to site 2. V1 is visit 1 so there is data for all clusters, v2 is 6 month visit so some of the clusters have no data.

data table3;
input id site cluster $ v1 v2;
CARDS;
1	1	a	1	1
2	1	a	1	1
3	1	a	1	1
4	1	a	1	1
5	1	a	1	1
6	1	b	1	.
7	1	b	1	.
8	1	b	1	.
9	1	b	1	.
10	1	b	1	.
11	2	c	1	.
12	2	c	1	.
13	2	c	1	.
14	2	c	1	.
15	2	c	1	.
16	2	d	1	1
17	2	d	1	1
18	2	d	1	1
19	2	d	1	1
20	2	d	1	1
;
run;

proc format; 
	value $clusterfmt 
	'a' = 'Cluster A'
	'b' = 'Cluster B'
	'c' = 'Cluster C'
	'd' = 'Cluster D'
;
run; 

proc tabulate data = table3 missing;
class site;
class cluster / preloadfmt order = data;
var v1 v2;
table site = ' ', cluster ALL = 'Total', v1/ printmiss misstext = '0';
table site = ' ', cluster ALL = 'Total', v2/ printmiss misstext = '0';
format cluster $clusterfmt.;
run;

[ballardw · Accepted solution]

---

@hr667 wrote:

I cannot share the data but I will do my best to describe what I'm talking about. This is generally what my code looks like (without all of the style and labeling information).

proc tabulate data = table3 missing;

class site;

class cluster / preloadfmt order = data;

var v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11;

table site = ' ', cluster ALL = 'Total', v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11/ printmiss misstext = '0';

format cluster $clusterfmt.;

run;

For each proc tabulate I want three tables, one for each site. And I want all of the clusters in each site to be visible in the corresponding site's table even when no data is available.

---

I think what you may be looking for is a CLASSDATA set containing the combinations of Site and Cluster that you want to appear in the report.

Here is an example that adds a value of Sex to the table that is not in the data (like preloadfmt) but is conditional on Age using the SASHelp.class data set.

data sexage;
   input age sex :$1.;
datalines ;
11 F
11 M
12 F
12 M
13 F
13 M
14 F
14 M
14 T
15 F
15 M
15 T
16 F
16 M
16 T
;


proc tabulate data=sashelp.class classdata=sexage;
   class sex ; 
   class age;
   var height;
   table age,
         sex,
         height*(min max mean)
   ;
run;
        
         

[hr667]

Thank you so much! That solved my problem perfectly.