topic Re: 3 cross result using proc report in SAS Procedures

3 cross result using proc report

pallis — Fri, 20 Sep 2013 05:11:02 GMT

How Can I Achieve 3 Cross result in Report Procedure.

Looking for output like this

students Score

subject JUN13 JUL13 AUG 13 Total Score

M F M F M F

-------------------------------------------------------------------------------

Maths x y x y x y z

I have achieved

students Score

subject JUN13 JUL13 AUG 13 Total Score

--------------------------------------------------------------------------------

I couldn't figure out how to group again student's score based on Gender. Can any one one give me hint.

Regards,

venky

Re: 3 cross result using proc report

Cynthia_sas — Fri, 20 Sep 2013 17:30:47 GMT

Hi:

It's not entirely clear to me what you need and since you didn't post your data or your existing code, your description of what you want isn't exactly clear. And, you've posted almost the same question previously in a different forum, so it seems that the answers that you received in that forum didn't help you.

Consider these PROC REPORT examples that uses SASHELP.PRDSALE. They show 2 different ways to get a TOTAL column on the far right of the report. Depending on what you mean by "group again student's score based on Gender", you would probably use some form of these methods to calculate multiple total columns -- depending on what you mean. Since SASHELP.PRDSALE is a common dataset, I would suggest that you carefully review the code below to understand what is happening to calculate the totals before you change the code to mimic whatever it is that you mean by "group again". SASHELP.PRDSALE has several other variables that could be used in place of GENDER -- for example, DIVISION or REGION, if you wanted another nesting.

There is a documentation topic entitled "How PROC REPORT Builds a Report" that is very useful in understanding the various usages, such as GROUP and ACROSS and DISPLAY and you may want to read more about using variables more than 1 time on a report through the use of aliases in the COLUMN statement. But although PROC REPORT is good for simple nestings, if you have a lot of crossings or nestings on your final report, PROC TABULATE might be a better report choice.

cynthia

title; footnote;

ods html file='c:\temp\report_with_total.html';

proc report data=sashelp.prdsale nowd;

title '1) Use COMPUTED variables';

column country product,(actual predict) ('Total' totact totprd);

define country / group;

define product / across;

define actual / sum f=dollar16.;

define predict / sum f=dollar16.;

define totact / computed 'Actual' f=dollar16.;

define totprd / computed 'Predict'f=dollar16.;

compute totact;

totact = sum(_c2_, _c4_, _c6_, _c8_, _c10_);

endcomp;

compute totprd;

totprd = sum(_c3_,_c5_, _c7_, _c9_, _c11_);

endcomp;

rbreak after / summarize;

run;

proc report data=sashelp.prdsale nowd;

title '2) Use ALIAS method';

column country product,(actual predict) ('Total' actual=totA predict=totP);

define country / group;

define product / across;

define actual / sum f=dollar16.;

define predict / sum f=dollar16.;

define totA / sum 'Actual' f=dollar16.;

define totP / sum 'Predict'f=dollar16.;

rbreak after / summarize;

run;

ods html close;

Re: 3 cross result using proc report

pallis — Tue, 24 Sep 2013 04:57:38 GMT

Thanks for the reply. All my variables data type is character. I need to calculate the count of the it based on group and month wise.

ex:

Code Date Pay_type

1 30-Jun-2013 Csh

1A 30-jun-2013 BOU

SAE 31-Jul-2013 NCL

-----------------------------------------

SAP 31-Aug-2013 NCL

As you observe that data type is character and date format.

This is how my data is. It contains around 4 million records.

I'm looking for this kind of output like below.

{Code}

proc report DATA=bncd_jun_jul_aug NOWD missing headline ;

COLUMN CODE STATUS Date ;

define CODE/ group ;

define STATUS / across '__Status__'width=3 right;

define Date/ order descending across group format=monyy7. ;

title2 'PROC REPORT' ;

run ;

JUN2013 JUL2013 AUG13

------------------------------------------------------------------------

Code BOU NCL CSH BOU NCL CSH BOU NCL CSH Total

-------------------------------------------------------------------------------------------------------

1 1 2 3 3 3 0 1 0 9 21

SAE

SAP

Re: 3 cross result using proc report

Cynthia_sas — Wed, 25 Sep 2013 16:22:03 GMT

Hi:

If you read the PROC REPORT documentation to which I referred you, it would explain how you can get statistics, such as the N or count very easily. PROC REPORT can nest across variables and can give you the N statistics and can give you a total at the far right of the report. Consider the PROC REPORT code using SASHELP.PRDSALE. This is a fake dataset, so there is not much variation in the N value for every cell, but the underlying PROC REPORT concepts should still apply. In my example, DIVISION and COUNTRY are both character variables and MONTH is a SAS date value. I limited the data to only the 1st quarter just to keep the report smaller. By default, when you only have group and across items in your COLUMN statement (such as DIVISION, COUNTRY and MONTH), the only statistic that REPORT can give you is the N or count. But you can explicitly place N on the COLUMN statement (as shown in the code) to get the TOTAL column. The PROC REPORT documentation explains the use of the comma (,) for nesting ACROSS items.

cynthia

title; footnote;

ods html file='c:\temp\report_with_char_var.html';

proc report data=sashelp.prdsale nowd;

where quarter = 1;

title '1) Use character variables';

column division month,country n;

define division / group;

define month / across order=internal f=monyy7. 'Date';

define country / across ' ';

define n / 'Total' f=comma8.;

rbreak after / summarize;

compute after;

division = 'Total';

endcomp;

run;

ods html close;

Re: 3 cross result using proc report

pallis — Thu, 26 Sep 2013 15:28:23 GMT

Thanks you M/S.Cynthia. I will run and update the thing.