BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ivs
Calcite | Level 5 ivs
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ivs
Calcite | Level 5 ivs
Calcite | Level 5

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

20 REPLIES 20
Reeza
Super User

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
Opal | Level 21

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

IVS

Why have you rejected the OUT= option?

ivs
Calcite | Level 5 ivs
Calcite | Level 5

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!

Reeza
Super User

Why not output directly to excel using tagsets?

Then you can email that file directly.

ivs
Calcite | Level 5 ivs
Calcite | Level 5

that would be great! what's the code?

ivs
Calcite | Level 5 ivs
Calcite | Level 5

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

Reeza
Super User

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

ivs
Calcite | Level 5 ivs
Calcite | Level 5

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

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!

Fugue
Quartz | Level 8

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

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

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;

ivs
Calcite | Level 5 ivs
Calcite | Level 5

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.

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
  • 2417 views
  • 3 likes
  • 5 in conversation