BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LMSSAS
Quartz | Level 8

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. 

 

 Lisa_Sessions_0-1645802567591.png

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;
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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
  • 650 views
  • 1 like
  • 2 in conversation