turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- CONDITIONAL Sum

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-04-2010 12:58 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-04-2010 02:31 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

03-09-2010 12:48 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-09-2010 01:16 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-09-2010 02:48 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-09-2010 02:55 PM

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