BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Reeza
Super User

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
Calcite | Level 5 ivs
Calcite | Level 5

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
Super User

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
Super User

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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|

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 2418 views
  • 3 likes
  • 5 in conversation