Help using Base SAS procedures

how to count in proc report

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

how to count in proc report

Hi All,

how to count gender  in proc report

 

proc report data=sashelp.class;

columns age sex height weight sex=a;

 

define sex/group;

define a /n;

run;

 

Thanks,

SS


Accepted Solutions
Solution
‎06-06-2018 11:05 AM
SAS Super FREQ
Posts: 9,371

Re: how to count in proc report

[ Edited ]
Posted in reply to Cynthia_sas

Hi:

  Using your data of 101 rows, you can just use N and PCTN directly in the COLUMN statement. Since you want the label for N to be different each time it appears, that means you have to use an alias for the 2nd reference to N in order to be able to specify a different label.

 

  I don't think it looks right not to have the percent for the NO row, but that's your requirement. BREAK AFTER ACTIVE did not look right, so I took it out and let the RBREAK stay:

use_n_pctn.png

 

  Note that if you use the PERCENT9.2 format for the percent number, that rounding will occur.

 

  Hope this helps,

Cynthia

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

Re: how to count in proc report

You would use a computed block:

http://www2.sas.com/proceedings/forum2008/188-2008.pdf

 

However, my recommendation is don't.  Proc report is a reporting procedure.  There are other procedures - summary, means etc. which are far better at manipulating the data, and those should be used instead.

SAS Super FREQ
Posts: 9,371

Re: how to count in proc report

[ Edited ]

Hi,

Proc Report has all the same statistics available as TABULATE except for kurtosis and skewness and the special PCTSUM statistics (and since you have the COMPUTE block, you can always calculate the ROWPCTN and ROWPCTSUM, COLPCTN and COLPCTSUM statistics in PROC REPORT).

 

You can use PROC REPORT for a report like the ones produced by the code below. Any of these examples will work. The N statistic can always be used in a COLUMN statement, directly. Other statistics need to be used with an analysis variable. Here are some examples:

proc report data=sashelp.class;
columns  sex n ('Mean' height weight);
define sex/group;
define n /'Count';
define height/mean f=6.2;
define weight/mean f=6.2;
run;

proc report data=sashelp.class;
columns  sex n age,(mean) height,(mean) weight,(min);
define sex/group;
define n / 'Count';
define age / analysis f=6.2;
define height/ analysis f=6.2;
define weight / analysis f=6.2;
run;

proc report data=sashelp.class;
columns sex age n height,(mean) weight,(min);
define sex/group;
define age / group;
define n / 'Count';
define height/ analysis f=6.2;
define weight / analysis f=6.2;
break after sex / summarize style=Header;
run;

PROC REPORT can do a lot of the same things as other summary procedures, the syntax is different. There are a lot of good papers and books on PROC REPORT.

 

Cynthia

Frequent Contributor
Posts: 76

Re: how to count in proc report

Posted in reply to Cynthia_sas

Thanks

active no of active statusno of status%
NO200open200 0
YES150open150 %42.85
TOTAL350 350 

 

how can we calculate %age of status (open) vs No of active (only YES). in separate column

 my output should be like in % column and also I need total

I think we can calculate like this  % =(150/(200+150)*100  but in NO row I have to get 0.

 

Thanks,

SS

SAS Super FREQ
Posts: 9,371

Re: how to count in proc report

[ Edited ]

Hi, Can you post some test data in the form of a datastep and show the actual code that you've tried?

In a PROC REPORT COMPUTE block, you could assign a value of 0 to the cell for %, if you need to, but without data to test with, it is hard to understand what you want to do. And the structure of the data makes a difference to how you write the PROC REPORT code, so without data and code, it's impossible to answer your question. (Also, in your formula, with PROC REPORT you don't need to multiply by 100 because the PERCENT format will automatically multiply by 100 for you. And in PROC TABULATE, you don't need to multiply by 100 because TABULATE automatically multiplies by 100 for percents. In any case, for PROC REPORT, you don't need the extra *100.)

Cynthia

Frequent Contributor
Posts: 76

Re: how to count in proc report

Posted in reply to Cynthia_sas

Hi,

test data.

data a;
infile cards;
input active$ status$;
cards;
NO OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
NO OPEN
NO OPEN
NO OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
NO OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
NO OPEN
NO OPEN
NO OPEN
NO OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
NO OPEN
NO OPEN
NO OPEN
NO OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
NO OPEN
NO OPEN
NO OPEN
NO OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
NO OPEN
NO OPEN
NO OPEN
NO OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
YES OPEN
NO OPEN
NO OPEN
NO OPEN
NO OPEN
YES OPEN

;
run;

proc report data=a out=test(drop=_BREAK_);
columns active n status n test;
define active/group;
define status / group;
define n / 'active Count';
define n / 'status Count';
define test / '%' computed;

break after active / summarize style=Header;
rbreak after/summarize;
compute test;
if active='YES' then test=n/n2.sum;
else test=0;
endcomp;
run;

Thanks,

 

SS.

SAS Super FREQ
Posts: 9,371

Re: how to count in proc report

[ Edited ]

Hi:
Thanks, I'm teaching today, so it will take me a while to use your data. The challenge that I see on a quick look is this:
1) you can't use 2 define statements for n. You'll have to use a different technique.
2) for a simple PCTN, you don't need to do your own division as you show with the TEST item. You can just use the PCTN statistic. You'll need to use format=PERCENT9.2 or PERCENT10.3 to get the right number and percent sign. Also, I don't think you want the BREAK AFTER ACTIVE, I think you just want the RBREAK statement to get the table you previously showed. And, you don't say what you want to appear on the TOTAL line -- do you want to still see a 0 for the NO but see 100% on the TOTAL line?

Cynthia

Solution
‎06-06-2018 11:05 AM
SAS Super FREQ
Posts: 9,371

Re: how to count in proc report

[ Edited ]
Posted in reply to Cynthia_sas

Hi:

  Using your data of 101 rows, you can just use N and PCTN directly in the COLUMN statement. Since you want the label for N to be different each time it appears, that means you have to use an alias for the 2nd reference to N in order to be able to specify a different label.

 

  I don't think it looks right not to have the percent for the NO row, but that's your requirement. BREAK AFTER ACTIVE did not look right, so I took it out and let the RBREAK stay:

use_n_pctn.png

 

  Note that if you use the PERCENT9.2 format for the percent number, that rounding will occur.

 

  Hope this helps,

Cynthia

Frequent Contributor
Posts: 76

Re: how to count in proc report

Posted in reply to Cynthia_sas

Thanks a lot  Cynthia,

as you said my NOs also should be in % but I want to see the % in total row too. like below

 

active active countstatusstatus count%
NO32open320.000%
YES69open6968.317%
TOTAL101 10168.317%
proc report data=a out=test(drop=_BREAK_);
columns active n status n=n2 pctn;
define active/group;
define status / group;
define n / 'active Count';
define n / 'status Count';
define pctn / '%' f=percent9.2;
/*break after active / summarize style=Header;*/
rbreak after/summarize style=Header;
compute pctn;
if active='NO' then do;
pctn=0;
end;
endcomp;
compute after;
active='TOTAL';

endcomp;
run;

If I run that above code I am getting 100% in TOTAL row but I looking for  68.32%.

 

 

Thanks,

SS

SAS Super FREQ
Posts: 9,371

Re: how to count in proc report

Hi:

  If that's what you want, then you'd have to use a temporary variable in PROC REPORT to save the YES percent value on the YES row and then instead of what I show for COMPUTE AFTER, where I have

pctn=.;

 

you would have something different...probably like:

 

compute pctn;
    if active = 'NO' then do;
       pctn = 0;
    end;
    else if active = 'YES' then do;
       yespct = pctn;
    end;
  endcomp;
  compute after;
    active = 'TOTAL';
    pctn = yespct;
  endcomp;

 

In the above code, the ELSE condition creates a temporary variable called YESPCT and the nice thing about PROC REPORT is that the temporary variable values are automatically retained. So in the COMPUTE AFTER block, at the bottom of the report, the temporary variable value for YESPCT can be assigned to the PCTN column on the final summary row of the report. Your original desired report had just 0, and for that you need the CALL DEFINE Statement on the NO row. But if you want 0.00% (which is technically not the right percentage number), then you don't need the CALL DEFINE that I originally showed.

 

  There are a couple of good books in the SAS Bookstore on PROC REPORT. If you decide you want to learn more about PROC REPORT and how it works, look for books by Jane Eslinger, Art Carpenter and Lisa Fine.

 

Hope this helps,

Cynthia

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 223 views
  • 0 likes
  • 3 in conversation