topic Re: how to count in proc report in SAS Procedures

how to count in proc report

sathya66 — Tue, 05 Jun 2018 09:03:45 GMT

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,

Re: how to count in proc report

RW9 — Tue, 05 Jun 2018 09:21:57 GMT

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.

Re: how to count in proc report

Cynthia_sas — Tue, 05 Jun 2018 13:26:47 GMT

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

Re: how to count in proc report

sathya66 — Tue, 05 Jun 2018 15:23:31 GMT

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,

Re: how to count in proc report

Cynthia_sas — Tue, 05 Jun 2018 15:55:15 GMT

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

Re: how to count in proc report

sathya66 — Tue, 05 Jun 2018 16:05:37 GMT

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.

Re: how to count in proc report

Cynthia_sas — Tue, 05 Jun 2018 18:32:55 GMT

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

Re: how to count in proc report

Cynthia_sas — Tue, 05 Jun 2018 21:36:53 GMT

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

Re: how to count in proc report

sathya66 — Wed, 06 Jun 2018 07:45:17 GMT

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,

Re: how to count in proc report

Cynthia_sas — Wed, 06 Jun 2018 14:40:18 GMT

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

Re: how to count in proc report

sharuu_00 — Thu, 20 Jun 2019 08:39:14 GMT

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

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

Re: how to count in proc report

Cynthia_sas — Thu, 20 Jun 2019 16:01:08 GMT

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

Re: how to count in proc report

sharuu_00 — Fri, 21 Jun 2019 01:04:35 GMT

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.

Re: how to count in proc report

Cynthia_sas — Fri, 21 Jun 2019 14:32:02 GMT

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

Re: how to count in proc report

sasuser123123 — Thu, 25 Jul 2019 12:32:13 GMT

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....

Re: how to count in proc report

Cynthia_sas — Thu, 25 Jul 2019 14:29:14 GMT

Hi:

Thanks for posting some data, but what PROC REPORT code have you tried? What is your destination of choice for the output report? HTML, RTF, PDF???

Also, I'm confused by your output. A01 student does not have any rows for Math2 or Math3. Why do you show student A01 on the output, but not the other students? That doesn't make sense to me. Either you want to see all the students and their summarized scores or you don't want the students. It doesn't make sense to me to only show A01 and not student A02 or A03.

Cynthia

Re: how to count in proc report

sasuser123123 — Thu, 25 Jul 2019 15:44:24 GMT

Proc report data=dataset;
Column student Subject marks;
Define student/group;
Define subject/group;
Define marks/analysis sum;
Break after student/summarize;
Run;

Thank you!!

Re: how to count in proc report

sasuser123123 — Thu, 25 Jul 2019 15:48:10 GMT

May Be I gave the wrong data to you...

Re: how to count in proc report

sasuser123123 — Thu, 25 Jul 2019 15:49:26 GMT

Thank you so much Cynthia_Sas for your response..

Re: how to count in proc report

Cynthia_sas — Thu, 25 Jul 2019 16:36:13 GMT

Do you still have a question or did your code work?
Cynthia