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