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....
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.