topic Re: proc tabulate output in SAS Procedures

proc tabulate output

ivs — Thu, 25 Jul 2013 00:00:02 GMT

hi! does anyone know how to output a dataset that looks exactly like the table that proc tabulate creates? Preferably without column headings all together.

many thanks in advance!

Re: proc tabulate output

Reeza — Thu, 25 Jul 2013 00:37:05 GMT

That depends on what you're proc tabulate looks like. For example, nested headings wouldn't be supported in a dataset.

You need to provide more info, specifically, what does your input look like and what you want your output to look like.

Re: proc tabulate output

PGStats — Thu, 25 Jul 2013 00:56:42 GMT

You may capture the table cell values with ODS OUTPUT. For example :

proc tabulate data=myData;

class myClassVar;

var myVar1 myVar2 myVar3;

table (myVar1 myVar2 myVar3), myClassVar*Mean;

ods output Table=myTable;

run;

Re: proc tabulate output

Peter_C — Thu, 25 Jul 2013 10:51:27 GMT

IVS

Why have you rejected the OUT= option?

Re: proc tabulate output

ivs — Thu, 25 Jul 2013 17:39:13 GMT

i have a large data set which i need to summarize into a pivot table: rows by ID, columns by time periods, and pivot is sum of units.

out option in proc tabulate puts out a data table that is just like my original source data I can not send this table as a report.

if anyone knows how to create a pivot table that i can put into excel - that would be very helpful. many thanks in advance!

Re: proc tabulate output

Reeza — Thu, 25 Jul 2013 17:43:34 GMT

Why not output directly to excel using tagsets?

Then you can email that file directly.

Re: proc tabulate output

ivs — Thu, 25 Jul 2013 17:45:02 GMT

that would be great! what's the code?

Re: proc tabulate output

ivs — Thu, 25 Jul 2013 18:47:35 GMT

found the tagsets - its is miserably slow. i do not think this is going to work at all.

Re: proc tabulate output

Reeza — Thu, 25 Jul 2013 18:59:34 GMT

Well your report seems simple enough, you should be able to run a proc means on it, and then proc transpose to get the data you want fairly simply. Then you can export that dataset to excel.

I don't know if the time to do that will be less than generating tagsets, though I wouldn't have called it miserably slow.

This all assumes your requirements as specified are correct, and no details are missing

Re: proc tabulate output

ivs — Thu, 25 Jul 2013 19:13:27 GMT

my issue is with the volume of data, not the missing details - that's why it is so slow.

i am exploring building a crosstab with proc sql - seems to be the best option at the moment.

thanks for your advice nonetheless.

Re: proc tabulate output

Reeza — Thu, 25 Jul 2013 19:20:13 GMT

My concern with a a SQL solution is that it would require you to know the time periods in advance/hard code them?

But maybe I'm wrong

Good Luck!

Re: proc tabulate output

Fugue — Sat, 27 Jul 2013 00:26:47 GMT

As pointed out in an earlier comment, it would be helpful to have a broad sample of the input data and what you expect the output to actually look like. What are the "time periods" (e.g. specific dates, day numbers (e.g. day1, day2), quarters, months, years)? Will the columns be restricted only to certain "time periods" (e.g. 2003 - 2013)? Is sparse data ok (e.g. January 1-5 exists, but not January 6-7), or do you need all "time periods" in a range?

Re: proc tabulate output

ivs — Mon, 29 Jul 2013 17:07:06 GMT

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

... so forth for id=2 until 201306...

3 201201 35.00

3 201202 39.00

3 201203 40.00

.... so forth for id=3 until 201306...

what i need to see is this:

id 201201 201202 201203 ... 201306

1 152.00 148.00 160.00

2 4.50 5.00 4.55

3 35.00 39.00 40.00

so i have about 200,000 ids for about 18 months worth of time. excel does not handle this kind of volume. proc tabulate runs fast and delivers results, but i am unable to output these results into a meaningful format (such as excel for example). tagsets option was running for a while and eventually stopped responding.

i solved this for time being with proc sql, but it is not very elegant. 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!

Re: proc tabulate output

Reeza — Mon, 29 Jul 2013 17:11:04 GMT

proc transpose....except variable names can't be 201202 so you need another way to name them, in my solution I've added an M before using prefix, but put the label as the month name for printing out.

I think there's a typo in your example and proc tabulate points it out quickly as well

data have;

input ID Month Balance;

cards;

1 201201 152.00

1 201202 148.00

1 201203 160.00

2 201201 4.50

2 201202 5.00

2 201203 4.55

3 201201 35.00

3 201202 39.00

3 201203 40.00

;

run;

proc transpose data=have out=want prefix=M;

by ID;

id month;

var balance;

idlabel month;

run;

Re: proc tabulate output

Reeza — Mon, 29 Jul 2013 17:19:35 GMT

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!

Re: proc tabulate output

Fugue — Mon, 29 Jul 2013 18:02:04 GMT

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.

Re: proc tabulate output

ivs — Mon, 29 Jul 2013 18:05:56 GMT

Reeza, thanks for pointing me to proc transpose. it is working but the code is this:

proc transpose data=have out=want let;

by ID;

id Month;

run;

you have to use option 'let' to allow for duplicate months.

thanks again, this is going to work for me nicely.

Re: proc tabulate output

Peter_C — Mon, 29 Jul 2013 22:40:53 GMT

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|

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

Re: proc tabulate output

Reeza — Mon, 29 Jul 2013 23:12:54 GMT

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.

Re: proc tabulate output

ivs — Mon, 29 Jul 2013 23:28:27 GMT

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.