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
- /
- Getting PROC FREQ Report Output With Another Forma...

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-03-2016 11:26 AM

Hello everyone,

I need to get Proc Freq report output as data set. I know that it is not possible to get the SAS procedures reports being data set with same structure. At this point, I need your help to create my desired output with alternative methods with alternative design. I created a sample data set as below and I need to get the results as data set in my following desired image.( As I mentioned as above, it shouldn't be in same view, It helps me to reach my goal)

If I get the data set then I need to make some additional calculation over consisted data set. I’m not sure whether I can do the following calculation or not.

I tried to express my calculation over the Excel cells. If I get the first output as data set maybe I can do the following calculation easily.

Frequency(1)/TotalFrequency(1)==>D6/D7==> 0.857143

Frequency(0)/TotalFrequency(0)==>C5/C7==> 0.8

Frequency(1)/Total==>D6/E6==>0.857143

(Frequency(0)+ Frequency(1))/(TotalFrequency(0)+ TotalFrequency(0))==> =(C5+D6)/(D7+C7) ==> 0.833333

(Frequency(1)+ Frequency(0))/(TotalFrequency(0)+ TotalFrequency(0))==> =(C6+D5)/(C7+D7)==> 0.166667

```
Data Have;
Length Target 8 Predicted 8;
Infile Datalines Missover;
Input Target Predicted;
Datalines;
1 0
1 1
1 1
0 0
0 0
0 0
0 0
0 1
1 1
1 1
1 1
1 1
;
Run;
PROC FREQ Data=Have;
Tables Target*Predicted / TotPct;
Ods Output OneWayFreqs=Want;
Run;
```

Thank you very much

Accepted Solutions

Solution

06-06-2016
04:15 PM

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

06-03-2016 04:34 PM

Proc Tabulate will show the Total-Total cell with total

```
proc tabulate data=have;
class target predicted;
table (target all='Total')*n="Frequency",
predicted all='Total'
;
run;
```

All Replies

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

06-03-2016 11:44 AM

Your probably best off writing it in SQL and then transposing it. You may also want to look into the sensitivity/specificity output available from proc freq.

http://support.sas.com/kb/24/170.html

```
Proc SQL;
Create table want as
Select
Sum(predicted=1 and target=1)/sum(predicted=1) as col1,
Sum(predicted=0 and target=0)/sum(predicted=0) as col2,
Etc....
From have:
Quit;
```

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

06-03-2016 01:10 PM

Hello Reeza,

Thank you for your suggestion, but I couldn't understand what should I do, sorry

I also think to do some addtional steps instead of get the output directly. But I can't get proper data set to calculate "Total" columns.

Actually, If I used PROC TABULATE out= statement and then If I do PROC TRANSPASE, I get some kind of data set, if I get the following data set, I can do your calculation which you did in your previous data set. But I'm little bir confused

```
Proc Tabulate DATA=Have Out=Have2;
Class Target /Order=Unformatted Missing;
Class Predicted /Order=Unformatted Missing;
Table Predicted, Target*N;
Run;
PROC TRANSPOSE DATA=Have2
OUT=Have3
PREFIX=Column
NAME=Source
LABEL=Label;
VAR N;
COPY Target Predicted;
RUN;
QUIT;
```

The foregoing code creates this data set,

The following output can provide me to calculate my desired output but I'm not use if it is the right method or if it is possible

Thank you,

Can Lütfü Yılmazer

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

06-03-2016 01:21 PM

Hi again Reeza,

You want me to do this calculation over the Raw data, I understood now. ı thought that you meant Output of Proc Freq .Let me try to reach my aim.

Thank you

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

06-03-2016 02:06 PM

Thank you very much Reeza,

The following code provides my desired output

```
PROC SQL;
CREATE TABLE Want AS
SELECT
Sum(Target=1 And Predicted=1)/((Sum(Target=1 And Predicted=1))+(Sum(Target=1 And Predicted=0))) As Sensitivity
,Sum(Target=0 And Predicted=0)/((Sum(Target=0 And Predicted=1))+(Sum(Target=0 And Predicted=0))) As Specificity
,Sum(Target=1 And Predicted=1)/(Sum((Target=1 And Predicted=1))+(Sum(Target=0 And Predicted=1))) As Precision
,((Sum(Target=1 And Predicted=1))+(Sum(Target=0 And Predicted=0)))/
((Sum(Target=0 And Predicted=1))+(Sum(Target=0 And Predicted=0))+(Sum(Target=1 And Predicted=1))+(Sum(Target=1 And Predicted=0))) As Accuracy
,((Sum(Target=1 And Predicted=0))+(Sum(Target=0 And Predicted=1)))/
((Sum(Target=0 And Predicted=1))+(Sum(Target=0 And Predicted=0))+(Sum(Target=1 And Predicted=1))+(Sum(Target=1 And Predicted=0))) As ErrorRate
FROM Have;
QUIT;
```

Lastly,

Is it possible to get just following report output values on PROC FREQ or PROC TABULATE? Not being data set, being Report

Thank you

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

06-03-2016 02:57 PM

It may be possible in proc tabulate but it would be difficult.

I would generate my dataset and then use proc report to display the data.

Solution

06-06-2016
04:15 PM

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

06-03-2016 04:34 PM

Proc Tabulate will show the Total-Total cell with total

```
proc tabulate data=have;
class target predicted;
table (target all='Total')*n="Frequency",
predicted all='Total'
;
run;
```

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

06-04-2016 07:52 AM

Vaov, ballardw, thank you very much this values are which I want.

To add some additional cosmetics in the report, is it possible to don't show the Frequency label or is it possible to show the frequency column for Predicted values?

To understand me better, you can check the following output.

Or

If I write empty string, borders are still came

```
proc tabulate data=Have;
class Target predicted;
table (Target all='Total')*n="",
predicted all='Total';
run;
```

,Thank you

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

06-04-2016 10:30 PM

Hi:

I believe you are missing a TABULATE option. Generally, ROW=FLOAT will get rid of the "blank" header, such as you point to with your arrow. Please refer to this documenation example:

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#n0fvrniil70dmin1r4kee...

cynthia

I believe you are missing a TABULATE option. Generally, ROW=FLOAT will get rid of the "blank" header, such as you point to with your arrow. Please refer to this documenation example:

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#n0fvrniil70dmin1r4kee...

cynthia

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

06-05-2016 12:25 AM

turcay wrote:

Vaov, ballardw, thank you very much this values are which I want.

To add some additional cosmetics in the report, is it possible to don't show the Frequency label or is it possible to show the frequency column for Predicted values?

I will say that I attempted to make the output as you requested. If you had provided the different appearance earlier I would would have put the labels in column instead of row and used the / row=float option to suppress the empty cell for the row labels.

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

06-05-2016 06:55 AM

Ballardw,

You are right, I realized my additional demand after you provided me the code, sorry. The following code suppressed the empty cell, thank you.

```
proc tabulate data=Have;
class Target predicted;
table (Target all='Total')*n="",
predicted all='Total'
```

/Row=Float;
run;

Lastly, is it possible to create the following one?

,Thank you

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

06-06-2016 02:05 PM

Do you want both a Row labeled "Frequency" and a Column with "Frq" or just the column label?

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

06-06-2016 04:16 PM

Hello ballardw,

Your response met my demand, thank you and sorry for changing my request after you created my desired output.

Thank you,