topic Re: How to do calculation with two rows selected? in SAS Procedures

How to do calculation with two rows selected?

Phoenix8527 — Tue, 12 Mar 2013 21:07:17 GMT

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.

Re: How to do calculation with two rows selected?

DBailey — Tue, 12 Mar 2013 22:01:20 GMT

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;

Re: How to do calculation with two rows selected?

PGStats — Tue, 12 Mar 2013 22:12:38 GMT

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

upcase(S.status_change) = "NOT FOUND" and S.status='N';

quit;

Re: How to do calculation with two rows selected?

anshulgoel — Wed, 13 Mar 2013 11:06:19 GMT

According to my understanding.....

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

Note: Take your dataset instead of a.....and do adjust notfound and nochange accordingly.(not found or no change).....or use UPCASE....

%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....

Re: How to do calculation with two rows selected?

Phoenix8527 — Wed, 13 Mar 2013 13:51:02 GMT

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

Re: How to do calculation with two rows selected?

Linlin — Wed, 13 Mar 2013 13:58:59 GMT

Would you please post your code?

Re: How to do calculation with two rows selected?

Phoenix8527 — Wed, 13 Mar 2013 14:13:39 GMT

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';

Re: How to do calculation with two rows selected?

Linlin — Wed, 13 Mar 2013 14:47:27 GMT

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 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

;

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

upcase(S.status_change) = "NOT FOUND" and S.status='N';

quit;

proc print;run;

Obs Tenure percentage

1 NBUS 32.6%

2 Season 28.0%

Re: How to do calculation with two rows selected?

Phoenix8527 — Wed, 13 Mar 2013 15:00:32 GMT

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