Searching for Ranges for character values

[malena]

Hello, I am searching for a long list of procedure codes in the APCD dataset- since some are in long ranges like this- I was trying to use this code but  i am getting an error message for the "-" on the ranges- is there a way to do this without having to write these out?

%let Outpat =
(
'95004'-'95199', '96900'-'96922', '96999', '99201'-'99215', '99241'-'99245',
'99341'-'99350', '99354'-'99355', '99357'-'99360', '99366'-'99368', '99374'-
'99381'-'99397' , '99432', '99450', '99455', '99460', '99499','99401'-'99405',
'99408'-'99429', '99606', '99607', 'T1015', '99050'-'99058', '0500F'-'0503F',
'90918'-'90925', '97802'-'97804', '99024', '99078', '99170'-'99175', '99195'-
'99500'-'99599', 'T1502', 'T1023'-'T1026', 'T1028'-'T1030', '0001F', 'G0101'-
'G0127', 'G0166'-'G0168', 'G0179', 'G0180'-'G0182', 'G0246'-'G0250', 
'G0317'-'G0327', 'G0344', 'G0372', 'G0402', 'G0438', 'G0439', 'G0466'-
'M0064', 'M0076', 'M1204', 'Q0081'-'Q0085', 'S0220', 'S0265', 'S0302', 
'S9075', 'S9083'-'S9090', 'S9381'-'S9401', 'S9436'-'S9474', 'S9490'-
'90792', '90801'-'90802', '90804'-'90824', '90826'-'90829', '90832'-'90834',
'90836'-'90847', '90849', '90853', '90855', '90857', '90862'-'90899', 'H0001'-
'H0012'-'H0014', 'H0016'-'H0019', 'H0021'-'H0030', 'H0033'-'H0034', 
'H0041'-'H0042', 'H0046'-'H2010', 'H2013'-'H2014', 'H2016'-'H2019', 
'G0177', '96150'-'96155', 'T1007'-'T1010', 'H2104', 'H5300', 'HIVE2',  'S9475'-'S9479', 'S9481'-'S9483', 'G0396', 'G0397', 'G0410'-'G0411',
'G0473', 'G8466', 'G8477', 'G8128', 'G8467', 'Q4094', 'T1006', 'T1012');

data test;

set apcd.medical (keep = MED_REVENUE_CODE MED_FROM_DATE_YEAR MED_MEDICAID
MED_PROC_CODE MED_AGE MED_ICD_PROC1-MED_ICD_PROC7) ;

array vars2 {*} MED_PROC_CODE MED_ICD_PROC1-MED_ICD_PROC7;
do j = 1 to dim(vars2);
if vars2[j] in (&Outpat.) then Outp= = Outp=+ 1;
end;
drop j;

run;

[PaigeMiller]

You can create a look-up table. Another choice: if you have these desired character values in a SAS data set, you can use a join in PROC SQL to get matches. Another choice: if you have these desired character values in a SAS data set, you can create working values for &OUTPAT via PROC SQL and the into :outpat clause, this might actually work best if you are going to use &OUTPAT inside an array. (Example)

So, are all these desired codes in a SAS data set (or other type of electronic repository such as Excel or database)?

The construct

'95004'-'95199'

really won't work, its not defined for character strings. It is (without the quotes) defined for numbers in some circumstances, but probably not this one — and some of your character strings have non-numeric characters, so I don't see how such a thing could work here anyway.

[ballardw]

Generic note: When asking about any Error include from the LOG the entire data step or procedure with all the notes, warnings and errors. Copy from the LOG, then on the forum open a text box using the </> icon and then paste the copied text. The text box is important to maintain the diagnostic characters that SAS often provides as the main message windows will reformat pasted text.

You attempted, in effect do this comparison:

196  data example;
197     if 'd123' in ('d120' - 'd130');
                             -
                             22
                             200
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
              a datetime constant, a missing value, iterator, (, ), ','.

ERROR 200-322: The symbol is not recognized and will be ignored.

198  run;

Since the - character is not a quoted value then it is an error.

Not much choice to but to provide explicit lists of values somewhere since you want exact matches. My personal choice if I am using this list multiple times would be to create a custom INFORMAT that returns the value 1 when one of those values is found. If you have those values in a data set somewhere that is fairly easy to do.

proc format ;
invalue proccode
'96900',
'96901',
'96902',
'96903',
'96904',
'96905',
'96906',
'96907',
'96908',
'96909',
'96910',
'96911',
'96912',
'96913',
'96914',
'96915',
'96916',
'96917',
'96918',
'96919',
'96920',
'96921',
'96922', 
'96999' =1
other = 0
;
run;

data example;
   input v1 $ v2 $ v3 $;
   array v(*) v1-v3;
   /* assumes you want to count how many values 
      are in the list
   */
   do i=1 to dim(v);
      outp= sum(outp,input(v[i],proccode.));
   end; 

datalines;
96903 A1111 96999
;

The informat returns a 1 when the value is included and 0 otherwise.

I do have formats with more elements than that list.

[yabwon]

How should range for:

'0500F'-'0503F'

'G0466'-'M0064'

and

'S9490'-'90792'

look like?

Bart

[yabwon · Accepted solution]

If you are able to modify that 3 "sub-lists" to a format: "PREFIX" & "Numerical SUFIX", e.g. 

from: 

'G0466'-'M0064'

to:

'G0466'-'G9999',   'M0001'-'M0064'

the following code should work for you (I commented out those 3 "strange ranges" for now):

[also check my comment(question) inside the macrovariable value] [EDIT in CODE!]

%let Outpat =
(
'95004'-'95199', '96900'-'96922', '96999', '99201'-'99215', '99241'-'99245',
'99341'-'99350', '99354'-'99355', '99357'-'99360', '99366'-'99368', '99374'-
'99381'-'99397' , '99432', '99450', '99455', '99460', '99499','99401'-'99405',
'99408'-'99429', '99606', '99607', 'T1015', '99050'-'99058', 
/*'0500F'-'0503F',*/
'90918'-'90925', '97802'-'97804', '99024', '99078', '99170'-'99175', '99195'-
'99500'-'99599', 'T1502', 'T1023'-'T1026', 'T1028'-'T1030', '0001F', 'G0101'-
'G0127', 'G0166'-'G0168', 'G0179', 'G0180'-'G0182', 'G0246'-'G0250', 
'G0317'-'G0327', 'G0344', 'G0372', 'G0402', 'G0438', 'G0439', 
/*'G0466'-'M0064', */
'M0076', 'M1204', 'Q0081'-'Q0085', 'S0220', 'S0265', 'S0302', 
'S9075', 'S9083'-'S9090', 'S9381'-'S9401', 'S9436'-'S9474', 
/*'S9490'-'90792', */
'90801'-'90802', '90804'-'90824', '90826'-'90829', '90832'-'90834',
'90836'-'90847', '90849', '90853', '90855', '90857', '90862'-'90899', 
'H0001'-'H0012'-'H0014', /* is this "all" from 1 to 14 ? */
'H0016'-'H0019', 'H0021'-'H0030', 'H0033'-'H0034', 
'H0041'-'H0042', 'H0046'-'H2010', 'H2013'-'H2014', 'H2016'-'H2019', 
'G0177', '96150'-'96155', 'T1007'-'T1010', 'H2104', 'H5300', 'HIVE2',  
'S9475'-'S9479', 'S9481'-'S9483', 'G0396', 'G0397', 'G0410'-'G0411',
'G0473', 'G8466', 'G8477', 'G8128', 'G8467', 'Q4094', 'T1006', 'T1012');

/*
data test0;
  array ___ $ 1 _123 _234-_236;
run;
proc print data = test;
run;
*/

data _null_;
  text = "&Outpat.";
  cw = countw(text, "(,)");
  put cw=;
  call execute("data valList; array ___ $ 1 ");
  do i = 1 to cw;
    w = scan(text, i, "(,)");
    w0 = countw(w, "(- )");
    if w0 > 2 then put "NOTE: list longer than 2 " w=; /* ADDED in EDIT */
    length w1 w2 $ 5;
    w1 = dequote(strip(scan(w,  1, "-")));
    w2 = dequote(strip(scan(w, -1, "-")));
    if w1 NE w2 then
      call execute("_" !! w1 !! "-_" !! w2);
    else
      call execute("_" !! w1);
  end;
  call execute("; stop; run;");
run;

proc transpose data = valList out = valList(keep=_name_);
  var _all_;
run;

data valList;
  set valList;
  _name_ = substr(_name_, 2);
run;

/* test data */
libname apcd (work);
data apcd.medical; 
  length MED_REVENUE_CODE MED_FROM_DATE_YEAR MED_MEDICAID
  MED_PROC_CODE MED_AGE MED_ICD_PROC1-MED_ICD_PROC7 $ 8;

  MED_ICD_PROC1 = '90804';
  MED_ICD_PROC4 = 'H0016';
  MED_ICD_PROC5 = 'H0017';
  MED_ICD_PROC6 = 'H0018';
  MED_ICD_PROC7 = 'H0019';
run;

data test;
  set apcd.medical (keep = MED_REVENUE_CODE MED_FROM_DATE_YEAR MED_MEDICAID
  MED_PROC_CODE MED_AGE MED_ICD_PROC1-MED_ICD_PROC7) ;

  if _N_ = 1 then
    do;
      if 0 then set valList;
      declare hash H(dataset:"valList");
      H.defineKey("_name_");
      H.defineDone();
    end;

  Outp = 0;
  array vars2 {*} MED_PROC_CODE MED_ICD_PROC1-MED_ICD_PROC7;
  do j = 1 to dim(vars2);
    if H.check(key:vars2[j]) = 0 then Outp = Outp+ 1;
  end;
  drop j _name_;
run;

Bart

[malena]

thank you! that worked