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
@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;
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.
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.
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;
@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;
Thank you so much! That solved my problem perfectly.
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.