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
- /
- BI
- /
- Enterprise Guide
- /
- Totaling a column and dividing it by the original ...

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

12-19-2013 04:13 PM

Hello - I am using enterprise guide 6.1. And what I am looking to do is sum a column and divide the total by one of the values that I added to get the percent of the total. We just recently upgraded our SAS from 4.3 to 6.1 and this same program in 4.3 had this as the computed column:

Paid/Sum(paid) - Which would give me the percent of the paid amount for the facility to the total paid amount.

when I do that same formula in SAS enterprise guide 6.1 it only gives me a value of 1 because it is simply dividing the value with itself.

Is there a formula or function that I could use to help me get a percent for the paid amount in SAS 6.1? I am using the computed columns - Advanced expression editor. Please help! Thank you.

Accepted Solutions

Solution

12-19-2013
04:34 PM

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

12-19-2013 04:34 PM

I think your group by clause is too restrictive, its unusual to group by calculated items (but sometimes necessary).

Try reducing it to only what you need, FACILITY is my guess...since I'm assuming you're calculating the stats over each facility.

All Replies

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

12-19-2013 04:16 PM

Can you post the query that doesn't work (view code from EG).

My guess is that a GROUP BY clause is missing.

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

12-19-2013 04:24 PM

PROC SQL;

CREATE TABLE WORK."Details" AS

SELECT t1.'Facility Name'n,

t2.LOS,

t2.Admits,

/* Avg LOS */

(t2.LOS/t2.Admits) LABEL="Avg LOS" AS 'Avg LOS'n,

t1.Charged,

t1.Allowed,

/* % of Discount */

((t1.Charged-t1.Allowed)/t1.Charged) LABEL="% of Discount" AS '% of Discount'n,

t1.Paid,

** /* % of Total Inpt */**

** (t1.Paid/SUM(t1.Paid)) LABEL="% of Total Inpt" AS '% of Total Inpt'n,**

/* Allowed/Day */

(t1.Allowed/t2.LOS) LABEL="Allowed/Day" AS 'Allowed/Day'n

I set the formula to bold that is not working.

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

12-19-2013 04:27 PM

Need the full query, especially the group by clause.

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

12-19-2013 04:31 PM

PROC SQL;

CREATE TABLE WORK."Details" AS

SELECT t1.'Facility Name'n,

t2.LOS,

t2.Admits,

/* Avg LOS */

(t2.LOS/t2.Admits) LABEL="Avg LOS" AS 'Avg LOS'n,

t1.Charged,

t1.Allowed,

/* % of Discount */

((t1.Charged-t1.Allowed)/t1.Charged) LABEL="% of Discount" AS '% of Discount'n,

t1.Paid,

** /* % of Total Inpt */**

** (t1.Paid/SUM(t1.Paid)) LABEL="% of Total Inpt" AS '% of Total Inpt'n,**

/* Allowed/Day */

(t1.Allowed/t2.LOS) LABEL="Allowed/Day" AS 'Allowed/Day'n

GROUP BY t1.'Facility Name'n,

t2.LOS,

t2.Admits,

(CALCULATED 'Avg LOS'n),

t1.Charged,

t1.Allowed,

(CALCULATED '% of Discount'n),

t1.Paid,

(CALCULATED 'Allowed/Day'n);

QUIT;

Solution

12-19-2013
04:34 PM

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

12-19-2013 04:34 PM

I think your group by clause is too restrictive, its unusual to group by calculated items (but sometimes necessary).

Try reducing it to only what you need, FACILITY is my guess...since I'm assuming you're calculating the stats over each facility.

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

12-19-2013 04:48 PM

It worked. I unchecked 'Automatically select groups' and it worked. Thank you.