BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenrscott827
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

View solution in original post

6 REPLIES 6
Reeza
Super User

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

My guess is that a GROUP BY clause is missing.

jenrscott827
Calcite | Level 5

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. 

Reeza
Super User

Need the full query, especially the group by clause.

jenrscott827
Calcite | Level 5

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;

Reeza
Super User

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.

jenrscott827
Calcite | Level 5

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 3525 views
  • 0 likes
  • 2 in conversation