I suspect that your Error message is caused by using both ORDER and DISPLAY for the CAT2 variable. Many procedures when seeing two, or sometimes more, settings for the same single expected value will use the LAST one encountered in the code. I am guessing that you have summarized the data when creating what appear to be year total variables and hence the DISPLAY of the CAT2 looks correct and the ORDER is ignored.
With what you show for the result, since no example data has been provided and have to make many guesses about what the actual raw data may have looked like, I would probable go with a Proc Tabulate approach.
Instead of the step(s) I expect you used to create the CAT2 variable, which I am assuming you had to make to force anything resembling a stacking of different variables into single column for the rows Tabulate would allow use of the raw data.
Plus if the data originally only had one observation per person with year as a value then you don't have to do what ever summary step you included to force the multiple column variables (note: an ACROSS variable on a YEAR variable might be more desirable in Proc Report anyway).
Here is an example of a raw data set with random creations of one observation per person represented and a Proc Tabulate approach. Note, if your actually have MISSING values for a variable you need to add the option MISSING to the Class statement for any grouping variable or the observation is excluded from the report. The order of variables that would appear as the row label would also tend to make the MISSING appear first. I generally create or modify variables so the order of appearance is under my control and replace Missing values with a code value and have a format to assign meaningful text.
I have provided one format as an example of how to include desired text. The PRELOADFMT option may not be needed but sometimes helps in the control of order of values in the resulting table.
data example;
do year=2020 to 2024;
do i= 1 to rand('integer',10000); /* make random number of records per year*/
/* rand table creates numeric values according to a table of
probabilites to randomly assign a sex, age and race for each
observation
*/
sex= rand('table',.6, .399, .001);
age= rand('table',.08, .21, .21, .17, .1695, .16, 0.0005);
Race= rand('table',.6, .2, .15, .5);
output;
end;
end;
run;
proc format;
value sex
1='Male'
2='Female'
3='Unknown'
;
run;
proc tabulate data=example;
class year;
class sex /preloadfmt;
class age race;
format sex sex. ;
table sex all='Sex total' age all='Age Total' race all='Race total' ,
(year=' ' all='All years')*n=''
/printmiss misstext=' ';
;
run;
The PRINTMISS with PRELOADFMT means that the row labels will appear even if there are no values for any of the columns, which might happen with your 'Unknown' sex.
The use of the YEAR variable to create the columns allows the ALL table option to create the total columns. Which means when the next Year of data is added the code for the table doesn't need to change (i.e. inserting a Year2025 then Year2026, Year2027 etc).
Unless your data is read odd I would expect the total row for each of the Sex, Age and Race variables to be the same for a given year. If not then you really need to provide an example data set of that in the form of a data step code. I believe this have been mentioned in more than one of your threads.
... View more
