Help using Base SAS procedures

proc tabulate output

Accepted Solution Solved
Reply
Contributor ivs
Contributor
Posts: 26
Accepted Solution

proc tabulate output

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!


Accepted Solutions
Solution
‎07-29-2013 02:05 PM
Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

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.

View solution in original post


All Replies
Super User
Posts: 17,819

Re: proc tabulate output

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.

Respected Advisor
Posts: 4,646

Re: proc tabulate output

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

PG
Valued Guide
Posts: 2,175

Re: proc tabulate output

IVS

Why have you rejected the OUT= option?

Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

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 Smiley Sad 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!

Super User
Posts: 17,819

Re: proc tabulate output

Why not output directly to excel using tagsets?

Then you can email that file directly.

Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

that would be great! what's the code?

Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

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

Super User
Posts: 17,819

Re: proc tabulate output

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 Smiley Happy

Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

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.

Super User
Posts: 17,819

Re: proc tabulate output

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 Smiley Happy

Good Luck!

Super Contributor
Posts: 307

Re: proc tabulate output

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?

Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

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!

Super User
Posts: 17,819

Re: proc tabulate output

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 Smiley Happy

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;

Solution
‎07-29-2013 02:05 PM
Contributor ivs
Contributor
Posts: 26

Re: proc tabulate output

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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