topic Re: Matching values to a list in another file in SAS Programming

Matching values to a list in another file

Wolverine — Tue, 24 Jul 2018 15:04:41 GMT

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;

Re: Matching values to a list in another file

RW9 — Tue, 24 Jul 2018 15:10:37 GMT

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.

Re: Matching values to a list in another file

Wolverine — Tue, 24 Jul 2018 15:17:03 GMT

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.

Re: Matching values to a list in another file

RW9 — Tue, 24 Jul 2018 15:29:54 GMT

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;

Re: Matching values to a list in another file

Wolverine — Thu, 26 Jul 2018 18:48:40 GMT

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