I have the following proc sql and output below. I am trying to solve for remove duplicates by pulling in the INDV_CHNL_PTNR_ID with the MAX (Cert_Ranking). The Cert_Ranking column comes from a case statement I created in the original table. The having clause I am using is not working. Does anyone have any suggestions?
proc sql; create table test as
select distinct
*
from MedicareSalesAgencies
having max(Cert_Ranking) and indv_chnl_ptnr_id
; quit;
INDV_CHNL_PTNR_ID | Cert_Ranking |
29 | 11 |
29 | 3 |
29 | 2 |
29 | 11 |
29 | 3 |
29 | 2 |
29 | 11 |
29 | 3 |
29 | 2 |
29 | 11 |
29 | 3 |
29 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
34 | 3 |
34 | 2 |
proc sql; create table test as
select
indv_CHNL_PTNR_ID, max(CERT_RANKING) as CERT_RANKING
from MedicareSalesAgencies
group by indv_CHNL_PTNR_ID
; quit;
Does that give you what you expect? If not, please post the desired outcome and we can help with the query.
proc sql; create table test as
select
indv_CHNL_PTNR_ID, max(CERT_RANKING) as CERT_RANKING
from MedicareSalesAgencies
group by indv_CHNL_PTNR_ID
; quit;
Does that give you what you expect? If not, please post the desired outcome and we can help with the query.
That gives me the desired outcome, Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.