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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
DBailey
Lapis Lazuli | Level 10

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;

PGStats
Opal | Level 21

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
Phoenix8527
Calcite | Level 5

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


Linlin
Lapis Lazuli | Level 10

Would you please post your code?

Phoenix8527
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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%

Phoenix8527
Calcite | Level 5

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

anshulgoel
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1382 views
  • 3 likes
  • 5 in conversation