Simplify This Proc SQL

[buechler66]

Hi.  I'm wondering if there isn't a simpler way to accomplish what my code below is doing. For each record on my table there is a CLM_BLG_PRVDR_NPI_NUM and a CLM_RFRG_PRVDR_NPI_NUM.  My requirement is to assign a Flag Indicator = 1 to each record where the associated CLM_RFRG_PRVDR_NPI_NUM value is not also the same value as ANY record's  CLM_BLG_PRVDR_NPI_NUM value on the entire table. Otherwise the Flag Indicator should be 0.

As you can see from my code below I accomplish this in two Proc SQL steps.  I'm interested to know if there is a simpler or more efficient way to get the same results in just one Proc SQL step?

Any insights would be much appreciated!

/* CREATE LIST OF RFRG NPI WHO HAVE NEVER BEEN A BLG NPI AND ASSIGN 1 AS A FLAG INDICATOR */
PROC SQL;
CREATE TABLE RFRG_NEVER_BLG AS
SELECT
DISTINCT CLM_RFRG_PRVDR_NPI_NUM
,1 AS FLAG_IND
FROM NFPP_TRD.&USERN._ORTHOTICS_BASE_PULL_2
EXCEPT
SELECT DISTINCT CLM_BLG_PRVDR_NPI_NUM
,1 AS FLAG_IND
FROM NFPP_TRD.&USERN._ORTHOTICS_BASE_PULL_2
;QUIT;

/* MERGE ON THE RFRG NPI WHO HAVE NEVER BEEN A BLG NPI AND SET AN INDICATOR FLAG */
PROC SQL;
CREATE TABLE F4_CL_ATRISK_V1 AS
SELECT T2.*
,CASE WHEN T1.FLAG_IND=1 THEN 1 ELSE 0 END AS FLAG_IND
FROM BASE_PULL_2 AS T2
LEFT JOIN RFRG_NEVER_BLG AS T1
ON T1.CLM_RFRG_PRVDR_NPI_NUM = T2.CLM_RFRG_PRVDR_NPI_NUM
;QUIT;

[Reeza]

What is the flag used for?
You can likely use the NODUPKEY or NOUNIQUEKEY to split the data into duplicates.

[PGStats · Accepted solution]

This?

PROC SQL;
CREATE TABLE F4_CL_ATRISK_V1 AS
select 
    *, 
    CLM_RFRG_PRVDR_NPI_NUM not in (select CLM_BLG_PRVDR_NPI_NUM from BASE_PULL_2) as flag
FROM BASE_PULL_2;
quit;

(untested)

[buechler66]

Thank you for taking the time to help. I appreciate it.

[SuryaKiran]

You can use the first query as a sub-query in second. 

PROC SQL;
CREATE TABLE F4_CL_ATRISK_V1 AS
SELECT T2.*, CLM_RFRG_PRVDR_NPI_NUM not in (SELECT DISTINCT CLM_RFRG_PRVDR_NPI_NUM
						FROM NFPP_TRD.&USERN._ORTHOTICS_BASE_PULL_2
						 WHERE CLM_RFRG_PRVDR_NPI_NUM not in ( SELECT DISTINCT CLM_BLG_PRVDR_NPI_NUM
											FROM NFPP_TRD.&USERN._ORTHOTICS_BASE_PULL_2)
											) as Flag
FROM BASE_PULL_2 AS T2
ON T1.CLM_RFRG_PRVDR_NPI_NUM = T2.CLM_RFRG_PRVDR_NPI_NUM
;QUIT;

 OR 

PROC SQL;
CREATE TABLE F4_CL_ATRISK_V1 AS
SELECT T2.*,CLM_RFRG_PRVDR_NPI_NUM not in (	SELECT DISTINCT CLM_RFRG_PRVDR_NPI_NUM
											FROM NFPP_TRD.&USERN._ORTHOTICS_BASE_PULL_2
										EXCEPT
											SELECT DISTINCT CLM_BLG_PRVDR_NPI_NUM
											FROM NFPP_TRD.&USERN._ORTHOTICS_BASE_PULL_2) as flag
FROM BASE_PULL_2 AS T2
;
QUIT;