topic Re: Filter on range of diagnosis codes that are strings in SAS Programming

Filter on range of diagnosis codes that are strings

kb011235 — Thu, 01 Jul 2021 22:08:27 GMT

Hi,

I was asked to find claims with diagnosis codes that are within a specific range, F320 to F339. The code I have almost works:

%let dx_start = F320 ; 
%let dx_end = F339 ; 

DATA dx_codes ;
	input diag $10. ;
	datalines ;
F22
F3010
F3013
F39
T1491XA
F320
F321
F322
F323
F324
F325
F330
F331
F332
F333
F3340
F3341
F3342
F338
F339
;
RUN ;

data want ;
  set dx_codes ;
	where "&dx_start." <=: diag <=: "&dx_end." ;
run;

The output is below where the values in red shouldn't be included. Seems as if sas is checking if diag is 'like' or 'between' dx_start and dx_end.

F320

F321

F322

F323

F324

F325

F330

F331

F332

F333

F3340

F3341

F3342

F338

F339

How can my code be modified to check for exact matches?

Re: Filter on range of diagnosis codes that are strings

PaigeMiller — Thu, 01 Jul 2021 22:19:08 GMT

So the ones you want always begin with F, that have 3 numbers (not 2 numbers, not 4 numbers) and the numbers must be between 320 and 339? Is that correct?

data want ;
  set dx_codes ;
  number_part=input(substr(diag,2),6.);
  if 320<=number_part<=339 and diag=:'F';
  drop number_part;
run;

If you want, you can replace the 320 and 339 with (modified) macro variables.

Re: Filter on range of diagnosis codes that are strings

kb011235 — Fri, 02 Jul 2021 00:09:42 GMT

Thanks, something similar is my backup method. I would really like understand why my solution isn't working and how to fix it if possible.

Re: Filter on range of diagnosis codes that are strings

Astounding — Fri, 02 Jul 2021 00:30:00 GMT

Your solution selects those extra records because of the colon in the comparisons:

<=:

That means for comparison purposes, truncate the longer string down to the length of the shorter string. So the comparisons are made on the basis of three characters (the length of the string in quotes). Just get rid of the colons:

Re: Filter on range of diagnosis codes that are strings

kb011235 — Fri, 02 Jul 2021 00:58:46 GMT

I get the same results.

Re: Filter on range of diagnosis codes that are strings

ChrisNZ — Fri, 02 Jul 2021 04:55:59 GMT

Try

where "&dx_start " <= DIAG<= "&dx_end " ;

Re: Filter on range of diagnosis codes that are strings

kb011235 — Fri, 02 Jul 2021 19:56:58 GMT

Same results.

I found an explanation of why it's happening:

https://support.sas.com/resources/papers/proceedings/proceedings/sugi26/p073-26.pdf

***********************************************************

In SAS, character strings must be adjusted to the same length
before they can be compared. When SAS compares character
strings without the colon modifier, it pads the shorter string with
blanks to the length of the longer string before making the
comparison. When SAS compares character strings with the
colon modifier after the operator, it truncates the longer string to
the length of the shorter string. This feature of the colon modifier
makes the comparison of a character string's prefix possible. For
example,
if zip='010' then do;
* This will pick up any zip which equals
'010' exactly;
if zip=:'010' then do;
* will pick up any zip starting with '010',
such as '01025','0103', '01098';
if zip>=:'010' then do;
* will pick up any zip from '010' up
alphabetically, such as '012', '21088';
where lastname gt: ‘Sm’;
* will pick up any last name alphabetically
higher than ‘Sm’, such as ‘Smith’,‘SNASH’,
‘Snash’;
The colon modifier can follow all comparison operators (=:, >=:,
<=:, ne:, gt:, lt:, in:) to conduct prefix comparison. The following
'in:' operation will select the students located in zip codes which
begin with '010', '011', '0131', '0138', respectively:
data s; set student;
if zip in:('010','011','0131','0138');

**************************************

Seems like

where "&dx_start." <= diag <= "&dx_end." ;

should work, but it doesn't.