DATA Step, Macro, Functions and more

Matching values to a list in another file

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Matching values to a list in another file

I'm working with medical claims data, and I need to identify claims that contain certain diagnosis, procedure, or revenue codes.  In the past, I've used the following approach with short lists of codes:

 

IF DIAGNOSIS_CODE_t in ("3991","3390")
 OR PRCDR_CDt in ("70450","70460","70470","70480","70481","70482","8703")
 OR UB_92_REV_CD in ("350","351","352","353","354","355","356","357","358","359")

    THEN IMGPROC=1;    ELSE IMGPROC=0;

However, the project I'm working on now involves thousands of codes that are stored in another file .  I've tried using PROC SQL to match the files on the desired variables, but that gets to be unwieldy because I have to do 3 different matches and then combine the files:

 

PROC SQL;
	Create table temp.IMG_ATH_qual_CTMRI_DX_match
		as Select a.*, b.*
	From temp.IMG_ATH_qual_DX_flag_recs_DXT2 a LEFT JOIN temp.HA_Tables b
	ON a.DIAGNOSIS_CODE_t = b.DX_code_HA_Table2;
QUIT;

PROC SQL;
	Create table temp.IMG_ATH_qual_CTMRI_pr_match
		as Select a.*, b.*
	From temp.IMG_ATH_qual_DX_flag_recs_procT2 a LEFT JOIN temp.HA_Tables b
	ON a.PRCDR_CODE = b.proc_code_HA_Table2;
QUIT;

PROC SQL;
	Create table temp.IMG_ATH_qual_CTMRI_rev_match
		as Select a.*, b.*
	From temp.IMG_ATH_qual_DX_flag_recs_revT2 a LEFT JOIN temp.HA_Tables b
	ON a.REVENUE_CODE = b.rev_code_HA_Table2;
QUIT;

Is there are way to do something like this?

IF DIAGNOSIS_CODE_t in (~~list of values in variable DX_code_HA_Table2 in file HA_Tables~~ )
 OR PRCDR_CDt in (~~list of values in variable proc_code_HA_Table2 in file HA_Tables~~)
 OR UB_92_REV_CD in (~~list of values in variable rev_code_HA_Table2 in file HA_Tables~~)

    THEN IMGPROC=1;    ELSE IMGPROC=0;

Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,840

Re: Matching values to a list in another file

Posted in reply to Wolverine

You can add as many of the subqueries as you like:

proc sql;
  create table want as 
  select ...
  from   have
  where diagnosis_code_t in (select code from codelist)
or abc in (...); quit;

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

Re: Matching values to a list in another file

Posted in reply to Wolverine

Yes, put your codes in a dataset, then sub-qeury that:

proc sql;
  create table want as 
  select ...
  from   have
  where diagnosis_code_t in (select code from codelist);
quit;
or
proc sql;
create table want as
select ...,
case when diagnosis_code_t in (select code from codelist) then 1 else 0 end as result
from have;
quit;

Please avoid coding in mixed or all upper case, it makes reading code very hard.

Contributor
Posts: 60

Re: Matching values to a list in another file

My concern with that approach is that I still have to generate 3 different files which then have to be combined, whereas the approach from my first example only requires 1 file.  The files can be quite large, so the first approach offers significant time savings.

Solution
3 weeks ago
Super User
Super User
Posts: 9,840

Re: Matching values to a list in another file

Posted in reply to Wolverine

You can add as many of the subqueries as you like:

proc sql;
  create table want as 
  select ...
  from   have
  where diagnosis_code_t in (select code from codelist)
or abc in (...); quit;
Contributor
Posts: 60

Re: Matching values to a list in another file

I didn't know you could that... it worked well.  Thanks! Smiley Very Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 63 views
  • 0 likes
  • 2 in conversation