topic trying to solve for "summing case staement results " in SAS Programming

trying to solve for "summing case staement results "

LMSSAS — Fri, 25 Feb 2022 15:32:11 GMT

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;

Re: trying to solve for "summing case staement results "

PaigeMiller — Fri, 25 Feb 2022 15:55:33 GMT

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;

Re: trying to solve for "summing case staement results "

LMSSAS — Fri, 25 Feb 2022 16:25:30 GMT

Thank you!