Creating a Distinct Row

[Jesusismygrace]

Hello,

I hope this makes sense

I would like to create the following table by grouping IPA_Num and IPA_Name:

IPA_Num         IPA_Name                                       Commercial Medicare
IP0000002001 SUTTER INDEPENDENT PHYSIC 1                  1

Instead, I am getting the below results

IPA_Num         IPA_Name                                       Commercial Medicare
IP0000002001 SUTTER INDEPENDENT PHYSIC 1
IP0000002001 SUTTER INDEPENDENT PHYSIC                      1

Code

/*Create unique list of IPA's from the enrollment file*/
Proc SQL;
Create table enroll_IPA as
Select distinct
memberenrollmentcustomcode2 as IPA_Num,
UPPER(memberenrollmentcustomcode4) as IPA_Name,
"" as Commercial,
"" as Medicare,
(CASE
When PayerCode in('C',) AND ProductCode in ('H','S','K') THEN 'Commercial'
When PayerCode in ('RR', 'NR', 'RM') AND ProductCode in('H') Then 'Medicare'
ELSE 'XX'
END) FORMAT=$30. AS Product
From enroll.ENROLLMENT_BIS_FINAL_COLLAPSED
Order by IPA_Num, IPA_Name
;
Quit;

Data enroll_IPA1 ;
set enroll_IPA;
if Product = 'XX' then delete;
if Product = 'Commercial' then Commercial=1;
if Product = 'Medicare' then Medicare=1;
Drop Product;
run;

[Patrick · Accepted solution]

Below one way to go - untested due to no sample data provided.

Proc SQL;
  Create table enroll_IPA1 as
    select 
      IPA_Num,
      IPA_Name,
      max(Commercial) as Commercial,
      max(Medicare) as Medicare
    from
      (
        Select 
          memberenrollmentcustomcode2 as IPA_Num,
          UPPER(memberenrollmentcustomcode4) as IPA_Name,
          CASE
            When PayerCode in('C',) AND ProductCode in ('H','S','K') THEN 1
            else 0
            END
            as Commercial,
          CASE
            When PayerCode in ('RR', 'NR', 'RM') AND ProductCode in('H') Then 1
            ELSE 0
            END
            as Medicare
        From enroll.ENROLLMENT_BIS_FINAL_COLLAPSED
      )
    group by 
      IPA_Num,
      IPA_Name
    having 
      Commercial=1 or Medicare=1
    ;
Quit;

[Jesusismygrace]

It works perfectly, Thank you!! Have a great evening