D. Studer
Reputation: 1875
SAS proc Tabulate, Order by specific variable
I am using ficticious numbers as I cannot publish the original dataset. I have the following SAS-Code:
PROC TABULATE DATA=table;
TITLE 'Offences per country and year;
CLASS year country offence_type;
TABLES country, year ALL, region /BOX="" MISSTEXT='0';
RUN;
This gives me for every country a table like this.
SWITZERLAND
year | theft | robbery | assault | …
------------------------------------
ALL | 1302 329 100 …
1980 | 321 100 24 …
1981 | 280 24 20 …
… | … … … …
Now, I'd like to change the order of the offence-types / columns. The most frequent offence-types (over all years) should appear on the left side and the less frequent offence-types should appear on the right side (as in the example above).
I've already tried / ORDER=FREQ after the TABLES-statement, but then also the order of the years gets changed.
Can anyone help? Thanks!
Upvotes: 0
Views: 3713
Answers (1)
Richard
Reputation: 27498
Specify the offence_type variable in a separate CLASS statement that uses the ORDER=FREQ option.
Example:
- First tabulate shows class order is data ascending,
- Second tabulate shows the effect of
CLASS <var>/ ORDER=FREQ. - Third tabulate shows the additional effect of
BY <group-vars>(and some trickery to avoid title above eachBYgroup).
ods listing;
options
pagesize=1000
nodate nonumber nocenter
formdlim=' '
formchar="|----|+|---+=|-/\<>*"
;
dm 'listing; clear';
data have(index=(country));
do country = 'Zeroland ', 'Switzerland', 'Crimeland';
do year = 1980 to year(today());
date = mdy(1,1,year);
do date = date to intnx('year',date,1);
crime = scan ('theft robbery assault parking', min(4,rand('geometric', 0.6)));
/***********************************************\
|* edit, make robbery predominant in Crimeland *|
\***********************************************/
if country = 'Crimeland' then do;
crime = scan ('robbery theft assault parking', min(4,rand('geometric', 0.6)));
end;
output;
end;
end;
end;
format date date9.;
run;
Tabulates
proc tabulate data=have NOSEPS format=8.;
title1 "Tabulate: default class ordering Alphabetical";
*where country like 'S%' and year < 1984;
where year < 1982;
class country year ;
class crime;
tables country, ALL year, crime / condense;
run;
proc tabulate data=have NOSEPS format=8.;
title1 "Tabulate: CLASS / order=freq - crimes Frequency Descending";
title2 "freq ordering based on all data";
* where country like 'S%' and year < 1984;
where year < 1982;
class country year ;
class crime / order=freq;
tables ALL country, ALL year, crime / condense;
run;
data for_tabulate(index=(country));
set have;
title1 = 'NOBYLINE, TITLE in Data. / order=freq by group';
title2 = 'Country ' || country;
run;
options nobyline;
proc tabulate data=for_tabulate NOSEPS format=8.;
title;
* where country like 'S%' and year < 1984;
where year < 1982;
by country;
class title1 title2 year ;
class crime / order=freq;
tables
title1 * title2,
ALL year,
crime
/ condense
;
run;
Listing output
Alphabetical
Tabulate: default class ordering Alphabetical
country Crimeland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| |assault |parking |robbery | theft |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 69| 50| 438| 176|
|year | | | | |
|1980 | 39| 29| 214| 85|
|1981 | 30| 21| 224| 91|
------------------------------------------------------------
country Switzerland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| |assault |parking |robbery | theft |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 70| 44| 178| 441|
|year | | | | |
|1980 | 42| 27| 84| 214|
|1981 | 28| 17| 94| 227|
------------------------------------------------------------
country Zeroland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| |assault |parking |robbery | theft |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 74| 38| 189| 432|
|year | | | | |
|1980 | 36| 25| 89| 217|
|1981 | 38| 13| 100| 215|
------------------------------------------------------------
Frequency (over all) descending
Tabulate: CLASS / order=freq - crimes Frequency Descending
freq ordering based on all data
All
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| | theft |robbery |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 1049| 805| 213| 132|
|year | | | | |
|1980 | 516| 387| 117| 81|
|1981 | 533| 418| 96| 51|
------------------------------------------------------------
country Crimeland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| | theft |robbery |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 176| 438| 69| 50|
|year | | | | |
|1980 | 85| 214| 39| 29|
|1981 | 91| 224| 30| 21|
------------------------------------------------------------
country Switzerland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| | theft |robbery |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 441| 178| 70| 44|
|year | | | | |
|1980 | 214| 84| 42| 27|
|1981 | 227| 94| 28| 17|
------------------------------------------------------------
country Zeroland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| | theft |robbery |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 432| 189| 74| 38|
|year | | | | |
|1980 | 217| 89| 36| 25|
|1981 | 215| 100| 38| 13|
------------------------------------------------------------
Frequency (over BY group) descending
title1 NOBYLINE, TITLE in Data. / order=freq by group
and title2 Country Crimeland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| |robbery | theft |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 438| 176| 69| 50|
|year | | | | |
|1980 | 214| 85| 39| 29|
|1981 | 224| 91| 30| 21|
------------------------------------------------------------
title1 NOBYLINE, TITLE in Data. / order=freq by group
and title2 Country Switzerland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| | theft |robbery |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 441| 178| 70| 44|
|year | | | | |
|1980 | 214| 84| 42| 27|
|1981 | 227| 94| 28| 17|
------------------------------------------------------------
title1 NOBYLINE, TITLE in Data. / order=freq by group
and title2 Country Zeroland
------------------------------------------------------------
| | crime |
| |-----------------------------------|
| | theft |robbery |assault |parking |
| |--------+--------+--------+--------|
| | N | N | N | N |
|----------------------+--------+--------+--------+--------|
|All | 432| 189| 74| 38|
|year | | | | |
|1980 | 217| 89| 36| 25|
|1981 | 215| 100| 38| 13|
------------------------------------------------------------
Upvotes: 1
Related Questions
- How to sort groups within a PROC SQL statement
- Sorting variables and proc report
- SAS PROC FREQ BY variable not sorted Ascending how to display table
- SAS: Change order of variable values in PROC TABULATE
- Proc Tabulate: Reordering a Formatted Variable
- PROC TABULATE Rearrange header subgroups independent of the data
- How to order columns created using Proc SQL in SAS
- SAS: proc Summary and proc Sort
- proc tabulate output not in alphabetical order
- SAS proc tabulate