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;
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% |
@SASJedi Thank you for the suggestion, it worked and was a much shorter path than what I did. I apreciate it
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: