BookmarkSubscribeRSS Feed
LMSSAS
Quartz | Level 8

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
SASJedi
Ammonite | Level 13

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
LMSSAS
Quartz | Level 8

@SASJedi Thank you for the suggestion, it worked and was a much shorter path than what I did. I apreciate it

 

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1653 views
  • 2 likes
  • 2 in conversation