SAS Programming

DATA Step, Macro, Functions and more
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

 

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

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
  • 1442 views
  • 2 likes
  • 2 in conversation