I am trying to solve for the following, my ouput is below for a visual: I want to sum 'cert_count' for the the 1's and the 0's grouped by aor_code. for examplel I want to have another column next to 'cert_count' that sums the number of 1's for aor_code K110 and does the same to count the number of 0's for aor_code K110. Can someone offer a solution? I tried to do a sum within the case statement and could not get it to run.
proc sql; create table Ready_To_Sale as
Select distinct
AGENCY_NAME,
AOR_CODE,
AGENCY_TYPE,
AGENCY_TIER,
AGENT_NAME,
AGENT_INDIVIDUAL_WRITING_NUMBER,
count (distinct LICENSE) as 'Agent_Count'n,
CERTIFICATION_STATUS,
COUNTY
From MedicareSalesAgencies
where CERTIFICATION_STATUS not in ('READY TO SELL 2021')
group by aor_code
;
quit;
Proc sql; Create table Ready_To_Sale2 as
Select distinct
*,
Case
when certification_status in ('READY TO SELL 2022') then 1 else 0 end as 'cert_count'n
From Ready_To_Sale a
;
Quit;
We cannot write code to work with data in a screen capture, data should be provided as SAS data step code and not in any other format. So this code is UNTESTED.
Proc sql;
Create table Ready_To_Sale2 as
Select distinct
*,
sum(certification_status eq 'READY TO SELL 2022') as cert_count
From Ready_To_Sale a
group by aor_code
;
Quit;
We cannot write code to work with data in a screen capture, data should be provided as SAS data step code and not in any other format. So this code is UNTESTED.
Proc sql;
Create table Ready_To_Sale2 as
Select distinct
*,
sum(certification_status eq 'READY TO SELL 2022') as cert_count
From Ready_To_Sale a
group by aor_code
;
Quit;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.