proc tabulate output

[Reeza]

But how do you know which month to stack things under then? e.g id 1 201202 has one entry so does it go under the first or second of id2 two 201202 entries?

It's weird to see duplicate columns...but as long as it makes sense to you then ok.

[ivs]

try the code i posted. each id has eighteen months associated with it. each month is therefore repeated as many times as there are ids. that's what option LET allows to deal with. by the way the code works for non-numerical fields as well. you just need to specify with VAR statement - in my example of data above if you had to transpose something like a character flag instead of balances.

thanks again for reminding me of proc transpose - i totally spaced out that it will work since i am not performing any calculation with the data, but simply rearranging it into a two-dimensional table.

[Reeza]

ivs wrote:

each id has eighteen months associated with it. each month is therefore repeated as many times as there are ids. 

It isn't 18 unique months is the issue.

In your sample you had 201202 twice for the same ID. LET takes the last observation for duplicates that would result in duplicate named columns.

here is what the data looks like:
ID     Month      Balance
1     201201     152.00
1     201202     148.00
1     201203     160.00
.... so forth for id=1 until 201306...
2     201202     4.50
2     201202     5.00
2     201203     4.55

The number of months per ID is irrelevant.

I didn't know about the LET option though, so thanks for that.

[Reeza]

What do you mean by headings stacked on to one another? Is proc tabulate really the only solution you want?

ivs wrote:
i'd still appreciate a solution with proc tabulate that outputs the results into an excel compatible format without all the column headings stacked up on top of each other as you see it in proc tabulate output. many thanks in advance if anyone has solution for this! 

[Fugue]

The solution provided by Reeza should work.

I would add that you can use PROC EXPORT to transfer the output from the transpose procedure into Excel, but I assume you already know that.

What version of Excel are you using? Excel 2003 has a limitation of 65,536 rows x 256 columns. If you are using Excel 2007 or later, then handling output of this size shouldn't be a problem.

[Peter_C]

once you learn tabulate you'll find it easy to understand how to collapse the headings, rather than have them stacked.

try

option nocenter ;

proc tabulate missing noseps format= 7.2 formchar='|----|+|---' ;

   class id month ;

   var balance ;

   table id=' ', month=' '*sum=' '*balance=' '

       / rts= 10 box='id';

run;

My listing looks like

The SAS System       

----------------------------------

|id      |201201 |201202 |201203 |

|--------+-------+-------+-------|

|1       | 152.00| 148.00| 160.00|

|2       |   4.50|   5.00|   4.55|

|3       |  35.00|  39.00|  40.00|

----------------------------------