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