## How to do calculation with two rows selected?

Solved
Occasional Contributor
Posts: 6

# How to do calculation with two rows selected?

Here is my table. I want to get the result group by Tenure. Percentage=1-(No Change,N/(Sum-Not Found,N)) e.g. for NBUS Percentage=1-(250/(517-146)), for Season Percentage=1-(917/(2028-680))

 Tenure Status_Change Status Count Sum NBUS Change N 95 517 NBUS No Change N 250 517 NBUS Not Found N 146 517 NBUS Change Y 0 517 NBUS No Change Y 23 517 NBUS Not Found Y 6 517 Season Change N 309 2028 Season No Change N 971 2028 Season Not Found N 680 2028 Season Change Y 2 2028 Season No Change Y 35 2028 Season Not Found Y 31 2028

Here is my code, it has ERROR: Subquery evaluated to more than one row.

proc sql;

create table table2 as select *, Sum(Count) as Sum from table1 group by Tenure;

proc sql;

create table table3 as

select Count/(select  (Sum-Count) as Dif from table2 where Status='N' and Status_Change='Not Found') from table2 where Status='N' and Status_Change='No Change';

Thanks a lot.

Accepted Solutions
Solution
‎03-12-2013 06:12 PM
Posts: 5,521

## Re: How to do calculation with two rows selected?

Partial understanding of what you want leads me to suggest :

proc sql;

select

C.tenure,

1 - C.count/(S.sum-S.count) as percentage format=percent7.1

from have as C inner join have as S

on C.tenure=S.tenure

where

upcase(C.status_change) = "NO CHANGE" and C.status='N' and

quit;

PG

PG

All Replies
Super Contributor
Posts: 578

## Re: How to do calculation with two rows selected?

It looks like you're grouping by tenure and status?

proc sql noprint;

select

t1.tenure,

,t1.status,

1 - (sum(case when status_change='No Change' then N else 0 end)/(sum(n) - sum(case when status_change='Not Found' then n else 0 end))

from

table1 t1

group by t1.tenure, t1.status;

quit;

Solution
‎03-12-2013 06:12 PM
Posts: 5,521

## Re: How to do calculation with two rows selected?

Partial understanding of what you want leads me to suggest :

proc sql;

select

C.tenure,

1 - C.count/(S.sum-S.count) as percentage format=percent7.1

from have as C inner join have as S

on C.tenure=S.tenure

where

upcase(C.status_change) = "NO CHANGE" and C.status='N' and

quit;

PG

PG
Occasional Contributor
Posts: 6

## Re: How to do calculation with two rows selected?

Thanks for your reply. I got an error message like this "ERROR: Ambiguous reference, column Tenure is in more than one table."

Super Contributor
Posts: 1,636

## Re: How to do calculation with two rows selected?

Occasional Contributor
Posts: 6

## Re: How to do calculation with two rows selected?

Hi Linlin,

Here is my code, it has ERROR: Subquery evaluated to more than one row.

proc sql;

create table table2 as select *, Sum(Count) as Sum from table1 group by Tenure;

proc sql;

create table table3 as

select Count/(select  (Sum-Count) as Dif from table2 where Status='N' and Status_Change='Not Found') from table2 where Status='N' and Status_Change='No Change';

Super Contributor
Posts: 1,636

## Re: How to do calculation with two rows selected?

Does PG's code give you what you want?

data have;

input Tenure :\$6. Status_Change :&\$9. Status \$1.Count sum;

cards;

NBUS Change  N 95 517

NBUS No Change  N 250 517

NBUS Change  Y 0 517

NBUS No Change  Y 23 517

Season Change  N  309 2028

Season No Change  N 971 2028

Season Change  Y 2 2028

Season No Change  Y 35 2028

;

proc sql;

create table want as

select

C.tenure,

1 - C.count/(S.sum-S.count) as percentage format=percent7.1

from have as C inner join have as S

on C.tenure=S.tenure

where

upcase(C.status_change) = "NO CHANGE" and C.status='N' and

quit;

proc print;run;

Obs    Tenure    percentage

1     NBUS        32.6%

2     Season      28.0%

Occasional Contributor
Posts: 6

## Re: How to do calculation with two rows selected?

Yes, I just got the correct answer from PG. Thanks a lot~

Occasional Contributor
Posts: 8

## Re: How to do calculation with two rows selected?

According to my understanding.....

This solution will help....if there is only one status_change as notfound or nochange....

%macro demo();
data k;
set a;
dif=sum-count;
if status_change in ('Nochange','Notfound');
run;

data percent(keep=percentage tenure);
set k;
if status_change='Nochange' then do;
call symput('temp',trim(left(count)));
end;

if status_change='Notfound' then do;
percentage = (symget('temp'))/trim(left(dif));
end;
if percentage>0;
run;

%mend;
%demo();

You can calculate percentage accordingly....

🔒 This topic is solved and locked.