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
SAS Super FREQ

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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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