topic Re: Divide attributes to get a percentage from proc sql in SAS Programming

Divide attributes to get a percentage from proc sql

LMSSAS — Wed, 23 Mar 2022 20:41:11 GMT

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;

Re: Divide attributes to get a percentage from proc sql

SASJedi — Wed, 23 Mar 2022 20:34:26 GMT

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%

Re: Divide attributes to get a percentage from proc sql

LMSSAS — Thu, 24 Mar 2022 12:49:52 GMT

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