BookmarkSubscribeRSS Feed
newbi
SAS Employee
I have report based on following sas table:

Fiscal_Year Fiscal_Qtr Week SaleRep Sales QtrGoal
2011 1 01/01 100 1000 500000
2011 2 03/01 100 1000 500000


I created the proc report which looks like:

Fiscal_Year 2011

SalesRep
100
---------------------------------------------------------------------------------------------
Fiscal_Qtr Week
1 01/01 $10000
2 03/01 $10000


How can i add QtrGoal after each qtr to proc report. I don't want to create another column for goal i would like to add it as row for end of each qtr.

Thanks
19 REPLIES 19
Cynthia_sas
SAS Super FREQ
Hi:
From what you posted of your data, it looks like they only had sales of $1,000 each quarter with a goal for $500,000 each quarter. So, I'm confused about 2 things:
1) If they only have sales of $1,000 each quarter, as shown in your data, how did the total on your desired report turn out to be $10,000 for each quarter???
2) what would you want to see at the summary line for the quarterly goal -- you said you wanted to see the quarterly goal as a summary line AFTER each quarter....that's going to look a bit cluttered...this is what I think you say you want:
[pre]

Fiscal_Year 2011

SalesRep
100

Fiscal_Qtr Week what is this column?
1 01/01 $10,000
Goal $500,000 <---you want this after EVERY quarter summary???
2 03/01 $10,000
Goal $500,000
[/pre]

or, maybe you have more data than you are showing??? It's not clear, since you only show 2 rows of data and 2 report rows with different numbers between the "data" and the "report". Also, would you want to see the sum of the Goal at the break or would you want to see the mean of the Goal at the break???

Let's try a different example. SASHELP.PRDSALE has quarterly data and it has 2 numeric variables ACTUAL and PREDICT. If I create this output with PROC REPORT, notice the summary values for ACTUAL and PREDICT at the break:
[pre]
Showing Totals of Actual and Predict for 2 Quarters

Predicted
Country Quarter Month Actual Sales Sales
GERMANY 1 01/01 $5,641.00 $5,432.00
02/01 $4,975.00 $4,530.00
03/01 $5,751.00 $7,511.00
Totals 1 $16,367.00 $17,473.00

2 04/01 $6,876.00 $5,119.00
05/01 $7,769.00 $5,265.00
06/01 $6,452.00 $4,524.00
Totals 2 $21,097.00 $14,908.00
[/pre]

What I think you want, for example, given the above data, would be:
1) not show the ACTUAL column on the report
2) have an additional summary or break line where the $16,367.00 or the $21,097.00 appeared on a line underneath underneath the summary for PREDICT.

Is this roughly what you want to do??

cynthia
newbi
SAS Employee
cynthia ,

Thanks for the reply and explantation. This is what i want


Fiscal_Year 2011

SalesRep SalesRep
100 200
Fiscal_Qtr Week NetSales NetSales
1 01/01 $10,000 $20,000
Goal $500,000 $500,000
2 03/01 $10,000 $50,000
Goal $500,000 $500,000

Goals are already calculated in query so in proc report i just want to display the value of goal end of each qtr for each sales rep with out any summarization. Once the goals are display i would like to create new row to calculate the diff between actual sales and goals.

Thanks
Cynthia_sas
SAS Super FREQ
Hi:

I'm still sort of confused by your requirements. It's not clear to me WHERE the difference would go. For example, would it look like this:
[pre]
SalesRep SalesRep
100 200
Fiscal_Qtr Week NetSales NetSales
1 01/01 $10,000 $20,000
Goal $500,000 $500,000
Difference $490,000 $480,000

2 03/01 $10,000 $50,000
Goal $500,000 $500,000
Difference $490,000 $450,000
[/pre]

Do you really only have 1 row for each quarter??? Usually, a quarter is composed of 3 months??

Also, it looks like you've changed your report. Originally, you only showed values for Sales Rep 100, now it looks like another Sales Rep has crept onto the report. At this point, how your input data -really- looks would be the determining factor for whether you used PROC REPORT or PROC TABULATE. I'm not sure what you mean when you say "Goals are already calculated in query". Are you already calculating summary data and then adding goal information or do you have to merge several files to get goal data into the sales data???

Can you share the PROC REPORT code that you've started with -- especially since the report has changed since your original posting -- seeing the actual code and a bit more of the actual data, too, would be helpful.

cynthia
newbi
SAS Employee
That's rigth the diff would go after the goal. And i will have more than 1 row per qtr. These are only test data. I will post the actual data set and proc report code but i'm having hard time formatting. Are you using any type of html to format the table in forum ?

Thanks
Tim_SAS
Barite | Level 11
See this post: http://support.sas.com/forums/thread.jspa?messageID=42922Ɦ for information about formatting code samples.
newbi
SAS Employee
Ok so here is my data set:

[pre]

Year Qtr WeekEnd Rep Sales QtrGoal
2010 3 7/3/2010 100 $113,754.50 $3,551,296.25
2010 3 7/3/2010 200 $118,793.00 $4,503,190.00
2010 3 7/10/2010 100 $256,828.50 $3,551,296.25
2010 3 7/10/2010 200 $291,422.50 $4,503,190.00
2010 3 7/17/2010 100 $227,376.50 $3,551,296.25
2010 3 7/17/2010 200 $266,476.50 $4,503,190.00
2010 3 7/24/2010 100 $231,753.00 $3,551,296.25
2010 3 7/24/2010 200 $277,350.00 $4,503,190.00
2010 3 7/31/2010 100 $236,743.50 $3,551,296.25
2010 3 7/31/2010 200 $286,673.50 $4,503,190.00
2010 3 8/7/2010 100 $265,156.50 $3,551,296.25
2010 3 8/7/2010 200 $309,149.50 $4,503,190.00
2010 3 8/14/2010 100 $222,688.00 $3,551,296.25
2010 3 8/14/2010 200 $289,689.00 $4,503,190.00

[/pre]

[pre]
Proc report data=work.goal;
/* Define columns */
Columns year qtr week_end rep, (Sales)QtrGoal;


define Year / group noprint ;
define Qtr / group "Fiscal Qtr";
define week_end /group format=YYmmdd9.;
define rep / group across;
define sales/ analysis;

break after qtr /Summarize style={font_weight=bold};

endcomp;

run;
[/pre]


[pre]
Year: 2010
Rep
100 200
Qtr Week End Sales Sales
3 7/3/2010 $113,754.50 $118,793.00
7/10/2010 $256,828.50 $291,422.50
7/17/2010 $227,376.50 $266,476.50
7/24/2010 $231,753.00 $277,350.00
7/31/2010 $236,743.50 $286,673.50
8/7/2010 $265,156.50 $309,149.50
8/14/2010 $222,688.00 $289,689.00
---------------------------------------------------------------------------------------------
Qtr Total $1,554,300.50 $1,839,554.00
Qtr Goal $3,551,296.25 $4,503,190.00
1st Qtr Achieved: QtrTotal/QtrGoal QtrTotal/QtrGoal

[/pre]
Cynthia_sas
SAS Super FREQ
Hi:
I wonder whether your parentheses are correctly placed in the posted code. When I run a version of your code with your data, this is what I get:
[pre]
rep
Fiscal 100 200
Qtr week_end sales sales qtrgoal
3 10-07-03 $113,754.50 $118,793.00 $8,054,486.25
10-07-10 $256,828.50 $291,422.50 $8,054,486.25
10-07-17 $227,376.50 $266,476.50 $8,054,486.25
10-07-24 $231,753.00 $277,350.00 $8,054,486.25
10-07-31 $236,743.50 $286,673.50 $8,054,486.25
10-08-07 $265,156.50 $309,149.50 $8,054,486.25
10-08-14 $222,688.00 $289,689.00 $8,054,486.25
3 $1,554,300.50 $1,839,554.00 $56,381,403.75
[/pre]

And, QTRGOAL doesn't look right to me. So, my guess is that either you are using some other program or the results you're getting aren't what you want.
Something like this makes a bit more sense to me (where you have every QTRGOAL underneath every sales rep:
[pre]
rep
Fiscal 100 200
Qtr week_end sales qtrgoal sales qtrgoal
3 10-07-03 $113,754.50 $3,551,296.25 $118,793.00 $4,503,190.00
10-07-10 $256,828.50 $3,551,296.25 $291,422.50 $4,503,190.00
10-07-17 $227,376.50 $3,551,296.25 $266,476.50 $4,503,190.00
10-07-24 $231,753.00 $3,551,296.25 $277,350.00 $4,503,190.00
10-07-31 $236,743.50 $3,551,296.25 $286,673.50 $4,503,190.00
10-08-07 $265,156.50 $3,551,296.25 $309,149.50 $4,503,190.00
10-08-14 $222,688.00 $3,551,296.25 $289,689.00 $4,503,190.00
3 $1,554,300.50 $24,859,073.75 $1,839,554.00 $31,522,330.00
[/pre]

...which shows the correct goal for each rep on every report row, but then summarizes them at the break -- probably not what you want either, but more in the realm of something that can be worked with in terms of how proc report works.

First, I think you have to get to something like this, where all the information is underneath every sales rep, before you worry about generating multiple summary lines:

[pre]
rep
Fiscal 100 200
Qtr week_end sales qtrgoal calcpct sales qtrgoal calcpct
3 10-07-03 $113,754.50 $3,551,296.25 3.20% $118,793.00 $4,503,190.00 2.64%
10-07-10 $256,828.50 $3,551,296.25 7.23% $291,422.50 $4,503,190.00 6.47%
10-07-17 $227,376.50 $3,551,296.25 6.40% $266,476.50 $4,503,190.00 5.92%
10-07-24 $231,753.00 $3,551,296.25 6.53% $277,350.00 $4,503,190.00 6.16%
10-07-31 $236,743.50 $3,551,296.25 6.67% $286,673.50 $4,503,190.00 6.37%
10-08-07 $265,156.50 $3,551,296.25 7.47% $309,149.50 $4,503,190.00 6.87%
10-08-14 $222,688.00 $3,551,296.25 6.27% $289,689.00 $4,503,190.00 6.43%
3 $1,554,300.50 $3,551,296.25 43.77% $1,839,554.00 $4,503,190.00 40.85%
[/pre]

The above report was generated with the following code -- which, because of the ACROSS item for REP, means that you have to use absolute column names in the calculation of the percent number (where SALES is divided by QTRGOAL) -- I know you don't care about this number on the individual rows, but since you are going to hide the QTRGOAL and the CALCPCT columns anyway and only use the numbers from the break, this is the easiest way to get REPORT to calculate the percent for you.

Can you verify that these are the numbers you expect, given your data? And, that you would, for example, under the SALES for REP 100, want to see one summary line with the $3,551,296.25 QTRGOAL and then another summary line with the SALES divided by QTRGOAL, which results in 43.77%??

cynthia
[pre]
ods listing;
Proc report data=work.goal nowd;
title '3) Calc Percent';
Columns year qtr week_end rep,(Sales QtrGoal calcpct);
define Year / group noprint;
define Qtr / group "Fiscal Qtr";
define week_end /group format=YYmmdd9.;
define rep / group across;
define sales/ analysis f=dollar16.2;
define qtrgoal /mean f=dollar16.2;
define calcpct /computed f=percent9.2;
compute calcpct;
** use absolute column numbers for ACROSS items;
_c6_ = _c4_ / _c5_;
_c9_ = _c7_ / _c8_;
endcomp;
break after qtr /Summarize style={font_weight=bold};
run;
[/pre]
newbi
SAS Employee
Cynthia,

That is what i'm looking for but i can't figure out how to hide the "QtrGoal" and "calcpct" columns.

[pre]
Rep
100 200
FiscalQtr week_end Sales qtrGoal calcpct Sales QtrGoal calcpct
3 7/3/2010 $113,754.50 $3,551,296.25 3.20% $118,793.00 $4,503,190.00 2.64%
7/10/2010 $256,828.50 $3,551,296.25 7.23% $291,422.50 $4,503,190.00 6.47%
7/17/2010 $227,376.50 $3,551,296.25 6.40% $266,476.50 $4,503,190.00 5.92%
7/24/2010 $231,753.00 $3,551,296.25 6.53% $277,350.00 $4,503,190.00 6.16%
7/31/2010 $236,743.50 $3,551,296.25 6.67% $286,673.50 $4,503,190.00 6.37%
8/7/2010 $265,156.50 $3,551,296.25 7.47% $309,149.50 $4,503,190.00 6.87%
8/14/2010 $222,688.00 $3,551,296.25 6.27% $289,689.00 $4,503,190.00 6.43%
3 $1,554,300.50 $3,551,296.25 43.77% $1,839,554.00 $4,503,190.00 40.85%

[/pre]

After hiding the qtrgoal and calcpct report should look like:

[pre]
Rep
100 200
FiscalQtr week_end Sales Sales
3 7/3/2010 $113,754.50 $118,793.00
7/10/2010 $256,828.50 $291,422.50
7/17/2010 $227,376.50 $266,476.50
7/24/2010 $231,753.00 $277,350.00
7/31/2010 $236,743.50 $286,673.50
8/7/2010 $265,156.50 $309,149.50
8/14/2010 $222,688.00 $289,689.00
3 Total Sales $1,554,300.50 $1,839,554.00
Qtr Goal $3,551,296.25 $4,503,190.00
Calcpct 43.77% 40.85%

[/pre]
Tim_SAS
Barite | Level 11
Use the NOPRINT option on the DEFINE statement.
Cynthia_sas
SAS Super FREQ
Hi:
How you "hide" columns or calculated items, but still have them available on the report is to use the NOPRINT option on the DEFINE statement. Just as you are using NOPRINT for the YEAR item, you would also use NOPRINT for the QTRGOAL and CALCPCT columns. But, you still need them so don't take them out of the COLUMN statement.

The LINE statement will do what you want (to write extra summary lines), but if your output is going to HTML, RTF or PDF output, there is no guarantee that the LINE statement "extra" information will line up underneath the SALES column the way you want. (only in the LISTING destination -- can you position your text strings written with the LINE statement. With other ODS destinations, your choices are to LEFT, CENTER or RIGHT justify the LINE text strings.)

That means you must use other techniques to generate the extra lines. This paper (on pages 7 and 😎 shows how to generate extra summary lines using PROC REPORT:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

The difference between what is in this paper and what you want to do is
1) the example only shows extra break lines at the end using "extra" variables named FLAG1 and FLAG2. You would have to create FLAG1 and FLAG2 so they were unique for every QUARTER.

2) Since you are using ACROSS items, you will have to use ABSOLUTE column names such as _c6_, _c7_, etc in your COMPUTE block. There are also examples of ABSOLUTE column names in the above paper.

The thing is that even if you use NOPRINT with items such as FLAG1 and FLAG2 or your QTRGOAL and CALCPCT columns, they still occupy a position on every report row and will get an absolute column number. To review, for example, the absolute column numbers that PROC REPORT would assign in the above code that I posted, you would use the OUT= option
[pre]
Proc report data=work.newgoal nowd ls=150 out=goalcols;
[/pre]

and then PROC REPORT will create an output dataset (WORK.GOALCOLS) that shows the absolute column numbers (with flag1 and flag2 added):
[pre]

year flag1 flag2 qtr week_end _C6_ _C7_ _C8_ _C9_ _C10_ _C11_ _BREAK_

2010 3 3 3 07/03 113754.5 3551296.25 0.03203 118793.0 4503190 0.02638
2010 3 3 3 07/10 256828.5 3551296.25 0.07232 291422.5 4503190 0.06471
2010 3 3 3 07/17 227376.5 3551296.25 0.06403 266476.5 4503190 0.05918
2010 3 3 3 07/24 231753.0 3551296.25 0.06526 277350.0 4503190 0.06159
2010 3 3 3 07/31 236743.5 3551296.25 0.06666 286673.5 4503190 0.06366
2010 3 3 3 08/07 265156.5 3551296.25 0.07466 309149.5 4503190 0.06865
2010 3 3 3 08/14 222688.0 3551296.25 0.06271 289689.0 4503190 0.06433
2010 3 3 3 . 1554300.5 3551296.25 0.43767 1839554.0 4503190 0.40850 qtr
2010 3 3 . . 1554300.5 3551296.25 0.43767 1839554.0 4503190 0.40850 flag2
2010 3 . . 1554300.5 3551296.25 0.43767 1839554.0 4503190 0.40850 flag1
[/pre]

So, you can see that _c6_ is the SALES column for REP 100 and _c7_ is the QTRGOAL for REP 100 and _c8_ is the CALCPCT for REP100. Then _c9_ is the SALES column for REP 200, _c10_ is the QTRGOAL for REP 200 and _c11_ is the CALCPCT column for REP 200. So, basically you have 3 report items for each sales rep going ACROSS.

Before you create your multiple summary lines, it is essential to understand how the absolute column numbers work. The reason is that you will need to assign the absolute column for QTRGOAL to the column for SALES at the break in a COMPUTE block. In the paper cited above, there were no ACROSS items in the example -- so simple report item names could be used. So you now have to be comfortable with 2 new techniques.

cynthia
** Code that created above output;
[pre]
data newgoal;
length flag1 flag2 $20;
infile datalines dlm=',';
input year qtr week_end : mmddyy10. rep $ sales : comma12. qtrgoal : comma12.;
flag1 = put(qtr,1.0);
flag2 = put(qtr,1.0);
return;
datalines;
2010,3,7/3/2010,100,113754.50,3551296.25
2010,3,7/3/2010,200,118793.00,4503190.00
2010,3,7/10/2010,100,256828.50,3551296.25
2010,3,7/10/2010,200,291422.50,4503190.00
2010,3,7/17/2010,100,227376.50,3551296.25
2010,3,7/17/2010,200,266476.50,4503190.00
2010,3,7/24/2010,100,231753.00,3551296.25
2010,3,7/24/2010,200,277350.00,4503190.00
2010,3,7/31/2010,100,236743.50,3551296.25
2010,3,7/31/2010,200,286673.50,4503190.00
2010,3,8/7/2010,100,265156.50,3551296.25
2010,3,8/7/2010,200,309149.50,4503190.00
2010,3,8/14/2010,100,222688.00,3551296.25
2010,3,8/14/2010,200,289689.00,4503190.00
;
run;

ods listing;
Proc report data=work.newgoal nowd ls=150 out=goalcols;
title '3) Calc Percent and show Abs Cols';
Columns year flag1 flag2 qtr week_end rep,(Sales QtrGoal calcpct);
define Year / group noprint;
define flag1 / group noprint;
define flag2 / group noprint;
define Qtr / group "Fiscal Qtr";
define week_end /group format=YYmmdd9.;
define rep / group across;
define sales/ analysis f=dollar16.2;
define qtrgoal /mean f=dollar16.2 ;
define calcpct /computed f=percent9.2 ;
compute calcpct;
** use absolute column numbers for ACROSS items;
_c8_ = _c6_ / _c7_;
_c11_ = _c9_ / _c10_;
endcomp;
break after flag1 /summarize;
break after flag2 / summarize;
break after qtr /Summarize style={font_weight=bold};
run;

proc print data=goalcols noobs;
title '3b) what are absolute columns';
format week_end mmddyy5.;
run;
[/pre]
newbi
SAS Employee
Thanks for all your help and explantion. I have one more questions. These were just test data so i had 2 sales rep. In production i may have sales rep anywhere from 3 to 15 depanding on region. So i would have to define each and every rep as ABSOLUTE for each reagion. Is the proc report the best option to create this style of report ? What other option do i have in SAS to create this style of report.

Thanks
Cynthia_sas
SAS Super FREQ
Hi:
The paper that I showed you has an example of using a SAS macro program to determine the number of ACROSS unique values for any run of the program and then macro code would generate the appropriate number of assignment statements in your COMPUTE blocks.

As always, I recommend having a working SAS program before you go down the macro program road, so that you know EXACTLY what type of assignment statements, etc your final program needs to have. So I'd start with everything working for 2 or 3 sales reps -- make sure that EVERYTHING else works -- such as the assignment statements and the labels for every report row, etc.

Another possible approach is to pre-summarize the data -- with PROC MEANS and/or PROC REPORT and/or PROC TABULATE and then calc the percent, get the extra summary lines into the data and then just use PROC PRINT or PROC REPORT to show the pre-summarized data.

The approach really depends on what you're more comfortable with -- DATA step programs or coding SAS Macro programs.

cynthia
newbi
SAS Employee
Cynthia,

Thanks for all your help.
Ksharp
Super User
It looks like the task for me. 🙂
OK. let me show you the power of line statement (My favorite).



[pre]
data goal;
infile datalines expandtabs;
input Year Qtr WeekEnd : mmddyy10. Rep Sales : dollar20. QtrGoal : dollar20. ;
format WeekEnd mmddyy10. Sales dollar20. QtrGoal dollar20. ;
datalines;
2010 3 7/3/2010 100 $113,754.50 $3,551,296.25
2010 3 7/3/2010 200 $118,793.00 $4,503,190.00
2010 3 7/10/2010 100 $256,828.50 $3,551,296.25
2010 3 7/10/2010 200 $291,422.50 $4,503,190.00
2010 3 7/17/2010 100 $227,376.50 $3,551,296.25
2010 3 7/17/2010 200 $266,476.50 $4,503,190.00
2010 3 7/24/2010 100 $231,753.00 $3,551,296.25
2010 3 7/24/2010 200 $277,350.00 $4,503,190.00
2010 3 7/31/2010 100 $236,743.50 $3,551,296.25
2010 3 7/31/2010 200 $286,673.50 $4,503,190.00
2010 3 8/7/2010 100 $265,156.50 $3,551,296.25
2010 3 8/7/2010 200 $309,149.50 $4,503,190.00
2010 3 8/14/2010 100 $222,688.00 $3,551,296.25
2010 3 8/14/2010 200 $289,689.00 $4,503,190.00
;
run;

ods listing close;
ods pdf file='c:\x.pdf' style=sasweb startpage=never;
options nobyline nodate nonumber;
ods escapechar='~';
title "~S={asis=on}Year: #byval1 %sysfunc(repeat(%str( ) ,50)) ";
proc report data=goal nowd out=temp;
by year;
column qtr weekend rep,(sales qtrgoal);
define qtr /group ;
define weekend/group;
define rep /across ;
define qtrgoal/analysis noprint;
compute qtrgoal;
if missing(_break_) then do;
c4=_c4_;
c6=_c6_;
end;

endcomp;
compute after qtr;
x1=_c3_/c4 ;
x2=_c5_/c6 ;
line '~S={just=right}Qtr Total: ' _c3_ dollar20. _c5_ dollar20.;
line '~S={just=right}Qtr Goal: ' c4 dollar20. c6 dollar20.;
line '~S={just=right}Qtr 'qtr best2.' Achieved: ' x1 percent20.2 x2 percent20.2;
endcomp;
run;
ods pdf close;
ods listing;
[/pre]


[pre]











~S={asis=on}Year: 2010

Rep
100 200
Qtr WeekEnd Sales Sales
3 07/03/2010 $113,755 $118,793
07/10/2010 $256,829 $291,423
07/17/2010 $227,377 $266,477
07/24/2010 $231,753 $277,350
07/31/2010 $236,744 $286,674
08/07/2010 $265,157 $309,150
08/14/2010 $222,688 $289,689
~S={just=right}Qtr Total: $1,554,301 $1,839,554
~S={just=right}Qtr Goal: $3,551,296 $4,503,190
~S={just=right}Qtr 3 Achieved: 43.77% 40.85%
[/pre]




Ksharp Message was edited by: Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1234 views
  • 0 likes
  • 4 in conversation