BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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)

 

PG

View solution in original post

4 REPLIES 4
Reeza
Super User
What is the flag used for?
You can likely use the NODUPKEY or NOUNIQUEKEY to split the data into duplicates.
PGStats
Opal | Level 21

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)

 

PG
buechler66
Barite | Level 11
Thank you for taking the time to help. I appreciate it.
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1383 views
  • 2 likes
  • 4 in conversation