Proc report

[deleted_user]

HI
a1 a2 a3 date amt
9072 0300 8000 01/23/2009 50000
9072 0700 5000 02/07/2009 60000
9072 0700 5000 02/08/2009 70000
9072 0800 9000 02/24/2009 67000
9072 0800 9000 02/25/2009 75000
9072 0100 4000 03/27/2009 45000
9072 0100 4000 03/28/2009 69000
9072 0700 5000 04/14/2009 75000
9072 0700 5000 04/15/2009 37670

and i need the output as shown below

a1 a2 a3 date amont
9072 300 8000 1/23/2009 50000

9072 700 5000 2/7/2009 60000
9072 700 5000 2/8/2009 70000
..............
total 130000
9072 800 9000 2/24/2009 67000
9072 800 9000 2/25/2009 75000
...................
total 142000
9072 100 4000 3/27/2009 45000
9072 100 4000 3/28/2009 69000
.......................
total 114000
9072 700 5000 4/14/2009 75000
9072 700 5000 4/15/2009 37670
...............
total 112670
so am using proc report for this
but plz suggest some solution

[sbb]

Recommend searching the SAS support http://support.sas.com/ website for SAS-hosted REPORT Procedure documentation and also technical/conference papers on this type of topic -- here is a Google advanced search argument to consider for some matches:

proc report subtotal site:sas.com

Scott Barry
SBBWorks, Inc.

[Cynthia_sas]

Hi:
PROC REPORT uses ORDER or GROUP variables to determine WHERE you can put your break in the final report. Based on your input data and your desired output, I'm having a hard time figuring out what the criteria are for when the break and summary should happen:

[pre]
a1 a2 a3 date amt
9072 0300 8000 01/23/2009 50000
------------------------------- you show a break here
9072 0700 5000 02/07/2009 60000
9072 0700 5000 02/08/2009 70000
------------------------------- you show a break here
9072 0800 9000 02/24/2009 67000
9072 0800 9000 02/25/2009 75000
------------------------------- you show a break here
9072 0100 4000 03/27/2009 45000
9072 0100 4000 03/28/2009 69000
------------------------------- you show a break here
9072 0700 5000 04/14/2009 75000
9072 0700 5000 04/15/2009 37670
------------------------------- you show a break here

[/pre]

At first, I thought the breaks were only by date, but perhaps the break should occur on unique combinations of a1, a2 and a3 for each month??? for each month/year?? And then you want a sub-total line that summarizes the AMT variable???

One issue I see with what you want to do is that for the first line of data:
[pre]
9072 0300 8000 01/23/2009 50000
[/pre]

it looks like you want the summary line suppressed because there is no summary under this line in your desired output. PROC REPORT will not suppress summary lines if you only have 1 line of detail. (there is no workaround for this issue)

Another issue will be that you show A1, A2, A3 repeated on every line of your output and if they are ORDER or GROUP variables, PROC REPORT would normally suppress the repetitious display or ORDER or GROUP variables, when values repeat. (using COMPUTED variables or other techniques, there is a workaround for this issue).

There are workarounds for some of these issues. If you follow scott's advice and look for proc report examples, you can find out how to accomplish this report with PROC REPORT.

Otherwise, this is the kind of report you can can get from PROC REPORT (shown below). If you need to suppress or not show the total line when there is only 1 line of detail ( as you show in your desired output), then you may want to move to DATA step techniques to produce this report.

cynthia
[pre]

a1 a2 a3 date Amount
9072 0300 8000 01/23/2009 50000
---------
50000

9072 0700 5000 02/07/2009 60000
9072 0700 5000 02/08/2009 70000
---------
130000

9072 0800 9000 02/24/2009 67000
9072 0800 9000 02/25/2009 75000
---------
142000

9072 0100 4000 03/27/2009 45000
9072 0100 4000 03/28/2009 69000
---------
114000

9072 0700 5000 04/14/2009 75000
9072 0700 5000 04/15/2009 37670
---------
112670
[/pre]

[deleted_user]

Hi

sorry for late reply............u showed some output below in your reply..........thata is the output am exactly rreferring to................so can u please send me the code..........i tried by using group and order but am not able to get exact output.............so can u send me the code plz


kumar

[Cynthia_sas]

Hi:
To make PROC REPORT break where you want, if I were going to solve this, I would create a new variable to be used only to control the break processing. Instead of trying to control breaking by A1, A2, A3 and date, I'd make one variable to control break processing.

In my program, I call this new variable SUPERORD and it is the concatenation of the values for A1, A2, A3, month and year. Then, I use the SUPERORD variable in my REPORT code as the ORDER variable and use BREAK AFTER SUPERORD to get the summary line on the report after each unique value of SUPERORD.

In the code below, SUPERORD is created when I read the raw data lines. However, it could just as easily be created right before the report step with a data step program:
[pre]
data test;
set your_dataset_here;
mm = put(month(date),z2.);
year = put(year(date),4.);
superord = catt(a1,a2,a3,mm,year);
run;
[/pre]


At any rate, the program below is the one that produced the output in my previous post. To see what the SUPERORD variable looks like, either do a PROC PRINT before the PROC REPORT step or remove the NOPRINT option from the PROC REPORT DEFINE statement for SUPERORD.

cynthia

[pre]
data test;
length superord $18;
infile datalines;
input a1 $ a2 $ a3 $ date : mmddyy10. amt;
mm = put(month(date),z2.);
year = put(year(date),4.);
superord = catt(a1,a2,a3,mm,year);
return;
datalines;
9072 0300 8000 01/23/2009 50000
9072 0700 5000 02/07/2009 60000
9072 0700 5000 02/08/2009 70000
9072 0800 9000 02/24/2009 67000
9072 0800 9000 02/25/2009 75000
9072 0100 4000 03/27/2009 45000
9072 0100 4000 03/28/2009 69000
9072 0700 5000 04/14/2009 75000
9072 0700 5000 04/15/2009 37670
;
run;

title; footnote;
ods listing ;
ods html file='c:\temp\superord.html' style=sasweb;
proc report data=test nowd;
column superord a1 a2 a3 date amt;
define superord / order order=data noprint;
define a1 /display;
define a2 / display;
define a3 / display;
define date/ f=mmddyy10. display;
define amt / sum 'Amount';
break after superord / summarize ol;
compute after superord;
line ' ';
endcomp;
run;
ods html close;


[/pre]