3 cross result using proc report

[pallis]

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

[Cynthia_sas]

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;

[pallis]

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

1A

SAE

SAP

[Cynthia_sas]

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;

[pallis]

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