How do I sum across rows and colums by multiple variables

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

How do I sum across rows and colums by multiple variables

Hello,

I am need of assistance.  As I am still a novice with SAS.  I need to sum variables Num2 and Den2 using values from Num and Denominator located in multiple rows and columns.

 

Row   Provider no.       Condition                        Num            Denominator        Num2                             Den2

1          1                       Heart Disease                500              525                      Num Total of  rows 1-3    Denom total rows 1-3                 

2          1                       Heart Disease                555              600

3          1                       Heart Disease                300              345

4          1                       Heart Failure                  200              250                   Total of Rows 1-5

5          1                       Heart Failure                  125              150

 

I have tried the First. Last. statement but am receiving an error (not sure if this is the best course of action). I also tried simply summing using proc sql but only receive the sum of all rows. Please advise. Thanks!

 

data want;

set Composite_C;

by 'provider Id'n conditions;

if first.'provider Id'n and first.condition then

do;

num2=0;

den2=0;

num2 + num;

den2 + denominator;

run;

 

proc sql;

create table want as

select 'provider Id'n,

condition,

sum(num) as num2

from work.composite_c;

quit;

 


Accepted Solutions
Solution
‎02-25-2016 04:02 PM
Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to Steelers_In_DC

Is my code right?  It is not the full code but the last 4 sections.

 

PROC SQL;

CREATE TABLE COMPOSITE_B AS

SELECT t1.'Provider ID'n,

t1.'Hospital Name'n,

t1.Condition,

t1.'Measure ID'n,

t1.'Measure Name'n,

t1.Score,

t1.Sample,

t1.Footnote,

t1.'Measure Start Date'n,

t1.'Measure End Date'n,

t1.MeasureName1,

t1.Num,

t1.Denominator,

t1.Rate,

t1.VolumeSelect

FROM WORK.COMPOSITE_A t1

WHERE t1.Condition = 'Heart Attack or Chest Pain' OR t1.Condition = 'Heart Failure' OR t1.Condition = 'Pneumonia'

OR t1.Condition = 'Surgical Care Improvement Project' OR t1.Condition = 'Children''s Asthma';

QUIT;

 

data Composite_C;

set work.composite_b;

if denominator = 'Not Available' then denominator = 0;

if num = 0 then Num2 = 0;

if denominator = 'Not Available' then Den2 = 0 ;

if denominator = 0 then Den2 = 0;

run;

 

proc sort data=Composite_C;

by 'provider ID'n condition;

run;

 

data want;

set Composite_C;

by 'provider ID'n condition;

num2+num;

Den2+denominator;

if first.condition then

do;

num2=num;

den2=denominator;

end;

run;

View solution in original post


All Replies
Super User
Posts: 5,438

Re: How do I sum across rows and colums by multiple variables

Please provide the desired output, it will hopefully describe your requirement a bit more.
Data never sleeps
Super User
Posts: 7,863

Re: How do I sum across rows and colums by multiple variables

data want;
set Composite_C;
by 'provider Id'n condition;
if first.condition
then do;
  num2=0;
  den2=0;
end;
num2 + num;
den2 + denominator;
if last.condition then output;
run;

try this, and post the log if errors occur.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to KurtBremser

Thank you Kurt but the code does not sum Num2 and Den2 as a running total by provider for each condition type.  I was expecting these final results in Num2 and Den2. Is there a way to modify the code to get the running total by provider and condition type?

 

Row   Provider no.       Condition                        Num            Denominator        Num2            ​                 Den2

1          1                       Heart Disease                500              525                     500                                   525         

2          1                       Heart Disease                555              600                     1055                                 1125

3          1                       Heart Disease                300              345                     1355                                1425

4          1                       Heart Failure                  200              250                     200 ​                                 250

5          1                       Heart Failure                  125              150                      325                                 400

Valued Guide
Posts: 860

Re: How do I sum across rows and colums by multiple variables

I think this is what you are looking for but I'm not sure.  Let me know:

 

data have;
infile cards dsd;
input Row Provider_no Condition$ Num Denominator;
cards;
1,1,Heart Disease,500,525
2,1,Heart Disease,555,600
3,1,Heart Disease,300,345
4,1,Heart Failure,200,250
5,1,Heart Failure,125,150
;run;

data want;
set have;
by Condition;
Num2 + num;
Den2 + denominator;
if first.Condition then do;
num2 = num;
den2 = denominator;
end;
run;

Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to Steelers_In_DC

It's still not giving the running total and the first value for NUM2 is null. (See output below).

 

Row   Provider no.       Condition                        Num            Denominator        Num2            ​​                 Den2

1          1                       Heart Disease                500              525                          .                      ​            525        

2          1                       Heart Disease                555              600                        555                               600

3          1                       Heart Disease                300              345       ​                 300                           ​     345

4          1                       Heart Failure                  200              250     ​                     .​                             ​      250

5          1                       Heart Failure                  125              150                        125                                 150

Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Hi Steelers_In_DC,

 

I tried your code again after resorting and I am still not getting the running total per condition for NUM2 and DEN2.

Valued Guide
Posts: 860

Re: How do I sum across rows and colums by multiple variables

Here is what I have for num2 and den2:

 

500    525
1055    1125
1355    1470
200    250
325    400

 

If that's not what you want please explain.  The only thing I would change is the by statement too:

 

by provider_no Condition;

Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to Steelers_In_DC

Yes, that is exactly what I want but I am not getting that results you are receiving.  I copied your code. This is how it looks below.

 

data want;

set Composite_C;

by 'provider ID'n condition;

num2+num;

Den2+denominator;

if first.condition then

do;

num2=num;

den2=denominator;

end;

run;

Valued Guide
Posts: 860

Re: How do I sum across rows and colums by multiple variables

show some results that you have that are wrong

Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to Steelers_In_DC

Below are actual results:

Provider ConditionNumDenominatorNum2Den2
10001Heart Attack Score576.24588576.24588
10001Heart Attack Score613.8620613.8620
10001Heart Attack Score0000
10001Heart Attack Score48.765348.7653
10001Heart Attack Score0000
10001Heart Attack Score0000
10001Heart Attack Score0000
10001Heart Attack Score0000
10001Heart Attack Score0000
10001Heart Failure Score446.34519446.34519
10001Heart Failure Score618618618618
10001Heart Failure Score184.32192184.32192
Valued Guide
Posts: 860

Re: How do I sum across rows and colums by multiple variables

I double checked with your new data and still get good results.  What you are showing doesn't make sense for the code that you say you are using.

Solution
‎02-25-2016 04:02 PM
Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to Steelers_In_DC

Is my code right?  It is not the full code but the last 4 sections.

 

PROC SQL;

CREATE TABLE COMPOSITE_B AS

SELECT t1.'Provider ID'n,

t1.'Hospital Name'n,

t1.Condition,

t1.'Measure ID'n,

t1.'Measure Name'n,

t1.Score,

t1.Sample,

t1.Footnote,

t1.'Measure Start Date'n,

t1.'Measure End Date'n,

t1.MeasureName1,

t1.Num,

t1.Denominator,

t1.Rate,

t1.VolumeSelect

FROM WORK.COMPOSITE_A t1

WHERE t1.Condition = 'Heart Attack or Chest Pain' OR t1.Condition = 'Heart Failure' OR t1.Condition = 'Pneumonia'

OR t1.Condition = 'Surgical Care Improvement Project' OR t1.Condition = 'Children''s Asthma';

QUIT;

 

data Composite_C;

set work.composite_b;

if denominator = 'Not Available' then denominator = 0;

if num = 0 then Num2 = 0;

if denominator = 'Not Available' then Den2 = 0 ;

if denominator = 0 then Den2 = 0;

run;

 

proc sort data=Composite_C;

by 'provider ID'n condition;

run;

 

data want;

set Composite_C;

by 'provider ID'n condition;

num2+num;

Den2+denominator;

if first.condition then

do;

num2=num;

den2=denominator;

end;

run;

Valued Guide
Posts: 860

Re: How do I sum across rows and colums by multiple variables

It took me a while to see it but you have to spell provider id the same way (case sensitive) as it is in the orginal table.

 

by 'Provider ID'n condition

 

Contributor
Posts: 42

Re: How do I sum across rows and colums by multiple variables

Posted in reply to Steelers_In_DC

Unfortunately, correcting the case sensitive name still will not sum across rows. I am not sure what this issue is on my side.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 638 views
  • 0 likes
  • 4 in conversation