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.
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
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;
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
Thanks for your reply. I got an error message like this "ERROR: Ambiguous reference, column Tenure is in more than one table."
Would you please post your code?
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';
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%
Yes, I just got the correct answer from PG. Thanks a lot~
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....
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.