proc tabulate output

[ivs]

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!

[Reeza]

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.

[PGStats]

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;

PG

[Peter_C]

IVS

Why have you rejected the OUT= option?

[ivs]

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!

[Reeza]

Why not output directly to excel using tagsets?

Then you can email that file directly.

[ivs]

that would be great! what's the code?

[ivs]

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

[Reeza]

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

[ivs]

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.

[Reeza]

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!

[Fugue]

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?

[ivs]

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!

[Reeza]

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;

[ivs · Accepted solution]

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.