topic Re: CONDITIONAL Sum in SAS Procedures

CONDITIONAL Sum

deleted_user — Thu, 04 Mar 2010 17:58:14 GMT

Hello All ,

I wnat to find the sum of all cells in the column ' Units Sold ' for which I have 'Week 1',..........,'Week 4' in the column " Week ".This is since I wnat to find a breakdown of the number of Units sold over 4 week time period.

I used code -

proc tabulate data=WEEK f=dollar8.;
class week Laptopmodel;
var UnitsSold;
table week*Laptopmodel,
Unitssold*Mean=" ";
run;

It gave me the breakdown by weeks , but didnt give total of the units sold over each period. I need to find the sum of units sold over the 4 week period. I'm not sure if Conditional Sum is possible in SAS . Can I find the sum of the no. of units sold over the 4 week period individually.

A sample data is -

Transaction Timeof Laptop Units
Obs ID DateofSale Sale Model Sold Warranty Week

1 RX000152 03/11/2009 11:27:53 AP3965 5 1 Week1
2 RX000170 04/11/2009 05:55:50 AT3600 5 1 Week1
3 RX000189 06/11/2009 09:07:45 AT3600 5 1 Week1
4 RX000179 05/11/2009 12:32:28 AP3965 5 0 Week1
5 RX000141 01/11/2009 08:49:26 AT3600 2 1 Week1
6 RX000149 02/11/2009 11:54:23 AP3965 2 1 Week1
7 RX000215 08/11/2009 14:09:49 AP3965 2 1 Week2
8 RX000230 09/11/2009 19:28:32 AP3965 2 1 Week2
9 RX000247 10/11/2009 14:14:41 AP3965 2 1 Week2
10 RX000295 14/11/2009 11:12:00 AP3965 2 1 Week2
11 RX000135 01/11/2009 09:50:39 AP3965 2 0 Week1
12 RX000143 02/11/2009 10:27:08 AP3965 2 0 Week1

Re: CONDITIONAL Sum

Cynthia_sas — Thu, 04 Mar 2010 19:31:52 GMT

Hi:
Proc TABULATE would not call that a CONDITIONAL SUM. What you want is a subtotal or total for ALL the values of a CLASS variable. If you look at my previous examples, here:
http://support.sas.com/forums/thread.jspa?threadID=8892&tstart=0
you will see that I used the universal class variable ALL in my other posting. If you look up ALL in the TABULATE documentation, you will see why I used it to get a GRAND TOTAL at the bottom of the table:
In particular, Example 6 at this site:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473755.htm
entitled "Summarizing Information with the Universal Class Variable ALL" should be very helpful.

And in the paper links I posted,
http://www2.sas.com/proceedings/sugi30/243-30.pdf (page 4, step 4 talks about using ALL)
http://www2.sas.com/proceedings/sugi30/258-30.pdf (page 4 also talks about ALL and ALL with generating percents)

This information is still available to you in the documentation and in the program code and the paper links previously posted.

cynthia

PS...an additional followup comment. If you want the SUM of total units sold, then you need to use the SUM statistic in your TABULATE code not the MEAN statistic.

Re: CONDITIONAL Sum

deleted_user — Tue, 09 Mar 2010 17:48:19 GMT

Hello Cynthia ,

could you please suggest a change in the code below as i get one log error that The type of name (type) is unknown.

I used the code -

/*Format period*/
PROC FORMAT;
VALUE perfmt 1='period1'
2='period2';
VALUE LapModfmt 1='AP3965'
2='AT3600';
value usetype 1='AP3965'
2='AT3600';
RUN;

/*Revenue breakdown for the two periods*/
proc tabulate data=period format=comma12.;
class period Week ;
var revenue ;
table period*(Week all='Subtotal')
all='Total for All Laptop Models'*f=dollar12.,
type='Laptop Models'*revenue=' '*sum=' '
all='Revenues from both Laptop Models'*revenue=' '*sum=' '
/ rts=25;
title 'revenues Each Laptop';
title2 '(Revenues in Dollars)';
RUN;

The log error -

2121 title2 '(Revenues in Dollars)';
2122 RUN;

ERROR: The type of name (type) is unknown.

Could you please suggest a change in the code.

Kind Regards ,
mark.

Re: CONDITIONAL Sum

sbb — Tue, 09 Mar 2010 18:16:13 GMT

Check this code piece from your PROC TABULATE:

revenue=' '*sum=' '

Some self-diagnosis by commenting out portions of the code to get back to a working program could help, also,

Scott Barry
SBBWorks, Inc.

Re: CONDITIONAL Sum

deleted_user — Tue, 09 Mar 2010 19:48:39 GMT

hello ,
in the code below :-

proc format;
value prdfmt 1='Period1'
2='Period2';
value wkfmt 1='Week1'
2='Week2'
3='Week3'
4='Week4';
run;

DATA revenue_breakdown;
set period;
FORMAT Period $prdfmt. Week $wkfmt.;
Title ' THe Data Set for revenue_breakdown ';
RUN;

LOG -

388 FORMAT Period $prdfmt. Week $wkfmt.;
--------
48
ERROR 48-59: The format $PRDFMT was not found or could not be
loaded.

2388! FORMAT Period $prdfmt. Week $wkfmt.;
-------
48
ERROR 48-59: The format $WKFMT was not found or could not be
loaded.

Kindly guide , where I'm making the mistake.

regards,
markc

Re: CONDITIONAL Sum

RickM — Tue, 09 Mar 2010 19:55:59 GMT

Putting "$" before a format name means the variable that you are associating that format with is character.