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
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.
Ready to level-up your skills? Choose your own adventure.