topic Re: Proc Tabulate in SAS Procedures

Proc Tabulate

Scottcom4 — Tue, 04 Nov 2008 00:41:54 GMT

Hi All,

I am attempting to create a table using proc tabs with Year and Month as the 2 classification variables. The thing is that I want to have Month in the correct calender order, however they appear in alphabetic order.

All I have been able to do so far is create a format with Month shown as 01 January for example. I would much rather not show the 01 if possible.

Does anyone have any other ideas/solutions?

Regards,

Scott

Re: Proc Tabulate

Scottcom4 — Tue, 04 Nov 2008 03:01:04 GMT

My apologies for wasting anyones time. I have finally worked it out.

By using the "order = data" option within the class statement the variable month is produced in the same order as the dataset to which it is based upon.

Thank you.

Re: Proc Tabulate

deleted_user — Tue, 04 Nov 2008 15:03:14 GMT

I don't like using order = data because I cannot always control all the class variables, and the default is at least consistent. Default ORDER= is internal, even though it shows and sums by the formatted value.
So, for month, store the full date value with monname. format.
A solution for year is less important, but the year4. format provides consistency.
Unfortunately TABULATE cannot use the same class variable with two different formats so you need that extra variable.
Here's a demo using sashelp.prdsal2[pre]data prdsal2/ view=prdsal2 ;
set sashelp.prdsal2 ;
if ranuni(1) > .2 ;
mon = monyr;
yr = mon ;
run;
proc tabulate missing noseps ;
class mon yr ;
format mon monname. yr year4. ;
var actual ;
table (all mon all ), (all yr all )
* n=' '*f= 5. / rts= 12 box='N' ;
run; [/pre]which provided this sumary in the listing window[pre]------------------------------------------------
|N | | yr | |
| | |-----------------------| |
| | All |1995 |1996 |1997 |1998 | All |
|----------+-----+-----+-----+-----+-----+-----|
|All |18501| 4676| 4576| 4620| 4629|18501|
|mon | | | | | | |
|January | 1557| 384| 397| 388| 388| 1557|
|February | 1520| 375| 379| 386| 380| 1520|
|March | 1549| 390| 382| 399| 378| 1549|
|April | 1545| 397| 382| 384| 382| 1545|
|May | 1533| 398| 356| 390| 389| 1533|
|June | 1554| 400| 389| 382| 383| 1554|
|July | 1544| 396| 371| 389| 388| 1544|
|August | 1523| 395| 377| 370| 381| 1523|
|September | 1557| 386| 396| 388| 387| 1557|
|October | 1547| 383| 382| 388| 394| 1547|
|November | 1522| 391| 380| 377| 374| 1522|
|December | 1550| 381| 385| 379| 405| 1550|
|All |18501| 4676| 4576| 4620| 4629|18501|
------------------------------------------------[/pre]

PeterC

Re: Proc Tabulate

Scottcom4 — Tue, 04 Nov 2008 22:59:26 GMT

Thanks Peter.

The output is as desired, but I am unsure how you are are getting the desired order.

Re: Proc Tabulate

Peter_C — Wed, 05 Nov 2008 10:29:47 GMT

the default is order=internal

The following is a bit like over stating the issue......
The internal values are traditional SAS date= number of days since 1960,
so the months of a year are ascending.
The summarising uses formatted values so the rows are summarised by the monname. format.
that's all

The important feature where TABULATE is adding value, is the distinction between internal value (for ordering) and formatted value (for presentation)

just mho

PeterC