- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 03-23-2022 03:26 PM
(1441 views)
I was able to make this work using append, proc transpose and then calculate percentages. It was a long way around but it worked. please disregard the original quesiton.
I have created two tables suing the below proc sql. I need to get the percentage of two attributes; RTS2022_Agent_Count divided by Appointed_Agents_Count and show in a percentage. Can someone please show me how to solve for this? Thank you!
proc sql; create table Appointed_Agents as
select distinct
TOH_AGENCY,
count (distinct AGENT_INDIVIDUAL_WRITING_NUMBER) as Appointed_Agents_Count
From TOH_Data
where Role in ('Agent')
/*and COURSE_TITLE IN ('2022 AEP MEDICARE CERTIFICATION')*/
group by TOH_AGENCY
;
quit;
proc sql; create table RTS2022_Agents as
select distinct
TOH_AGENCY,
count (distinct AGENT_INDIVIDUAL_WRITING_NUMBER) as RTS2022_Agent_Count
From TOH_Data
where Role in ('Agent')
and CERTIFICATION_STATUS IN ('READY TO SELL 2022')
group by TOH_AGENCY
;
quit;
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I'm pretty sure this would do it...
proc sql;
select aa.TOH_AGENCY
,RTS2022_Agent_Count
,Appointed_Agents_Count
,RTS2022_Agent_Count / Appointed_Agents_Count format=percent5.1 as Percentage
from RTS2022_Agents ra
inner join
Appointed_Agents aa
on aa.TOH_AGENCY=ra.TOH_AGENCY
;
quit;
TOH_AGENCY | RTS2022_Agent_Count | Appointed_Agents_Count | Percentage |
---|---|---|---|
a | 3 | 8 | 38% |
b | 3 | 10 | 30% |
c | 4 | 8 | 50% |
d | 1 | 10 | 10% |
e | 2 | 9 | 22% |
Check out my Jedi SAS Tricks for SAS Users
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@SASJedi Thank you for the suggestion, it worked and was a much shorter path than what I did. I apreciate it