turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How to - Get Proc Tabulate's Report Output Being A...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-30-2016 01:06 PM

Hello everyone,

I would like to get PROC TABULATE's report output as a data set. But as we know, it is not possible to get the data set by using OUT= option in PROC TABULATE. So that, I decided to use PROC MEANS procedure and get the same structure with PROC TABULATE's report output. I almost got the same output except the "Target"(0&1) values. How can I get these values in my desired data set.

Can somebody help me, please?

```
Data Have;
Length Predicted 8 Target 8 Bucket 8;
Infile Datalines Missover;
Input Predicted Target Bucket;
Datalines;
0.7 0 1
0.6 0 2
0.8 1 3
0.7 0 2
0.6 0 3
0.8 1 1
0.7 0 3
0.6 0 1
0.8 1 2
0.7 0 1
0.6 0 2
0.8 1 3
0.7 0 2
0.6 0 1
0.8 1 3
;
Run;
Ods Listing Close;
ODS OUTPUT Summary=Want(Rename=(MEAN=_MEAN MIN=_MIN MAX=_MAX MEDIAN=_MEDIAN N=_N));
PROC MEANS Data=Have MEAN MIN MAX MEDIAN N STACKODSOUTPUT;
VAR Predicted;
CLASS Bucket;
Run;
Ods Output Close;
Ods Listing;
PROC TABULATE DATA=Have FORMAT=BEST12.8 Out=Have;
VAR Predicted;
CLASS Target / ORDER=UNFORMATTED MISSING;
CLASS Bucket / ORDER=UNFORMATTED MISSING;
TABLE /* Row Dimension */
Bucket,
/* Column Dimension */
Predicted*(Mean Min Max Median) Target*N N;
RUN;
```

Thank you

Accepted Solutions

Solution

07-01-2016
10:01 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to turcay

06-30-2016 09:22 PM

Hi:

I do NOT believe that you will every get Target 0/1 numbers as you want, from PROC MEANS, in the same way that you can do with PROC TABULATE. However, (and I am not just saying this because I really like PROC REPORT), PROC REPORT can give you EXACTLY what you want in a report format and a DATASET format, as shown in the attached output:

So rather than spin your wheels trying to make PROC MEANS do something that PROC TABULATE or PROC REPORT could do better, why not just use PROC TABULATE if all you need is report output or use PROC REPORT if you need a report and/or a dataset.

cynthia

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to turcay

06-30-2016 01:19 PM

Actually PROC TABULATE does support OUT= on the PROC statement. Maybe that will make things easier.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

06-30-2016 02:27 PM

Hello @Astounding,

Thank you for trying to help me But I know the PROC TABULATE supports OUT= option but I want to see the following data set. How can I get this? PROC TABULATE OUT= optipn does not brings this data set.

Solution

07-01-2016
10:01 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to turcay

06-30-2016 09:22 PM

Hi:

I do NOT believe that you will every get Target 0/1 numbers as you want, from PROC MEANS, in the same way that you can do with PROC TABULATE. However, (and I am not just saying this because I really like PROC REPORT), PROC REPORT can give you EXACTLY what you want in a report format and a DATASET format, as shown in the attached output:

So rather than spin your wheels trying to make PROC MEANS do something that PROC TABULATE or PROC REPORT could do better, why not just use PROC TABULATE if all you need is report output or use PROC REPORT if you need a report and/or a dataset.

cynthia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

07-01-2016 09:46 AM

Thank you very much @Cynthia_sas,

Yes, you are right but after I got the output I will do some additional calculations so that is the reason why I select the PROC SQL because doing some calculations over the PROC SQL is easier than the PROC REPORT calculation. But in terms of performance, I do not have much knowledge. Maybe PROC REPORT is better than PROC SQL with regard to performance.

Thank you,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to turcay

06-30-2016 10:24 PM

Use SQL instead.

```
Data Have;
Length Predicted 8 Target 8 Bucket 8;
Infile Datalines Missover;
Input Predicted Target Bucket;
Datalines;
0.7 0 1
0.6 0 2
0.8 1 3
0.7 0 2
0.6 0 3
0.8 1 1
0.7 0 3
0.6 0 1
0.8 1 2
0.7 0 1
0.6 0 2
0.8 1 3
0.7 0 2
0.6 0 1
0.8 1 3
;
Run;
proc sql;
select Bucket,
mean(Predicted) as mean,
min(Predicted) as min,
max(Predicted) as max,
median(Predicted) as median,
sum(Target=0) as Target_0,
sum(Target=1) as Target_1,
(select count(*) from have) as n
from have
group by Bucket;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

07-01-2016 04:09 AM

Hello,

@Ksharp Thank you fro trying to help me.

But in EG 5.1, Median function gives the following error. And this warning lead to prevent grouping calculation. Do you have an idea how to avoid this situation?

WARNING: The MEDIAN function has been called with only one argument. However, it is not an SQL aggregate function, and this call

will not cause SQL aggregation.

Thank you,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to turcay

07-01-2016 04:35 AM

What version of SAS are you using ? if it is not SAS9.4 , you can't use MEDIAN() in SQL.

The workaround way is computing MEDIAN in PROC MEANS ,then merged it back to the SQL's dataset .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

07-01-2016 05:16 AM

Yes, It is not 9.4 It is 9.4 okay I will merge PROC MEANS Median with PROC SQL output.

Thank you

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to turcay

07-01-2016 09:39 AM

Or, you could use PROC REPORT, which does give you the MEDIAN function and would not require more than 1 pass through the data.

Just sayin'

cynthia

Just sayin'

cynthia