Help using Base SAS procedures

How to do calculation with two rows selected?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

TenureStatus_ChangeStatusCountSum
NBUSChangeN95517
NBUSNo ChangeN250517
NBUSNot FoundN146517
NBUSChangeY0517
NBUSNo ChangeY23517
NBUSNot FoundY6517
SeasonChangeN3092028
SeasonNo ChangeN9712028
SeasonNot FoundN6802028
SeasonChangeY22028
SeasonNo ChangeY352028
SeasonNot FoundY312028

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
Respected Advisor
Posts: 4,932

Re: How to do calculation with two rows selected?

Posted in reply to Phoenix8527

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;

PG

PG

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: How to do calculation with two rows selected?

Posted in reply to Phoenix8527

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
Respected Advisor
Posts: 4,932

Re: How to do calculation with two rows selected?

Posted in reply to Phoenix8527

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;

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?

Posted in reply to Phoenix8527

Would you please post your code?

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?

Posted in reply to Phoenix8527

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%

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?

Posted in reply to Phoenix8527

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 348 views
  • 3 likes
  • 5 in conversation