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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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