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;
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)
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)
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.