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;
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;
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.
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.
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;
I didn't know you could that... it worked well. Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.