how to count in proc report

[sathya66]

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

[RW9]

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.

[Cynthia_sas]

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

[sathya66]

Thanks

active	no of active	status	no of status	%
NO	200	open	200	0
YES	150	open	150	%42.85
TOTAL	350		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

[Cynthia_sas]

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

[sathya66]

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.

[Cynthia_sas]

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

[Cynthia_sas · Accepted solution]

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:

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

  Hope this helps,

Cynthia

[sathya66]

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 count	status	status count	%
NO	32	open	32	0.000%
YES	69	open	69	68.317%
TOTAL	101		101	68.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

[Cynthia_sas]

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

[sharuu_00]

I'm confused now. I tried above tips and still not working for me.

so, here is my final dataset:

x1.   x2    x3.   x4

1       0.    1.     1

0.      0.    1.     1

1.      1.    0.     1

1.      0.    0.     0

1.      0.     0.    0

I need to count how many ones for each variable. and produce the proc report as:

       x1.   x2.  x3.  x4

n.     4.     1.    2.    3

[Cynthia_sas]

Hi:
I'm confused. is it that you don't need the percents anymore? Why do your numbers all have periods after them. What code have you tried, this seems to be a different type of report than your previous report.

A value of 0 would "count" as a non-missing observation as shown in the example code and output below:

Are your values 0 or . (missing) or should the 0 values be ignored??? For example, if the 0 values were missing or ., then you'd get this:

Cynthia

[sharuu_00]

Hi,

I entered fake variables and as I entered and given space period appeared thats all.  I need to only count the ones for each variable.

[Cynthia_sas]

Hi:

  If you only need to count the variables with 1, then the SUM statistic will work in this case. (It would NOT work if your values were 3 and 5, for example, but in this case, you can use SUM to generate what you want with either PROC REPORT or PROC TABULATE, as shown below:

Hope this helps,

Cynthia

[sasuser123123]

Data.is
Student Subject Semister Marks
A01 math1 1 60
A02. Math1 1 76
A03. Math1 1 67
A01. Math1 2 60
A02. Math2 2 70
A03. Math3 2 69
A01. Math1 3 69
A02. Math2 3 56
A03. Math3 3 80


How to get the total Marks of math1 ,math2 and math3
For all the semisters
Like
A01. Math1 189
Math2 192
Math3. 207
.
.



By using proc report how we will get output like this....