topic Simplify This Proc SQL in SAS Programming

Simplify This Proc SQL

buechler66 — Fri, 05 Oct 2018 17:09:52 GMT

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;

Re: Simplify This Proc SQL

Reeza — Fri, 05 Oct 2018 17:20:25 GMT

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

Re: Simplify This Proc SQL

PGStats — Fri, 05 Oct 2018 18:07:13 GMT

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)

Re: Simplify This Proc SQL

SuryaKiran — Fri, 05 Oct 2018 18:45:16 GMT

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;

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;

Re: Simplify This Proc SQL

buechler66 — Fri, 05 Oct 2018 18:48:34 GMT

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