Using SAS macro with substr

[CathyVI]

Hello,

I think it's possible but am not sure how to do it. I have a CCW data with multiple ICD variables. The ICD variables are in this format ICD_DGNS_CD1, ICD_DGNS_CD2, and so on. 

I am writing a sql code to pull all OUD within the data using the ICD codes. In the where statement, I don't want to write the entire variables out (e.g., icd_dgns_cd1) in my code so am think there should be a macro code to call the ICD variables. I tried something like ; where  (substr(icd_&,1,5) in (&icd.) but it didn't work. 

%let icd= '30400', '30401', '30402', '30403', '30470', '30471', '30472', '30473',...... ; 
proc sql;
create table oud as
select id, clm_id, dob_dt, icd_dgns_cd1, icd_dgns_cd2, icd_dgns_cd3, icd_dgns_cd4,
icd_dgns_cd5, icd_dgns_cd6, icd_dgns_cd7, icd_dgns_cd8
from bcarclms2021 
where  (substr(icd_dgns_cd1,1,5) in (&icd.) or substr(icd_dgns_cd2,1,5) in (&icd.) or  
        substr(icd_dgns_cd3,1,5) in (&icd.) or substr(icd_dgns_cd4,1,5) in (&icd.) or
        substr(icd_dgns_cd5,1,5) in (&icd.) or substr(icd_dgns_cd6,1,5) in (&icd.) or
        substr(icd_dgns_cd7,1,5) in (&icd.) or substr(icd_dgns_cd8,1,5) in (&icd.) ; 
quit;

[Tom]

I don't see any attempt to use macro code posted.  Just normal SQL code.  Is that the SQL code you want to use macro code to create?

So perhaps something like this?

%macro query(indata,outdata,nvar,icdlist);
%local i var;
proc sql;
create table &outdata as
select id, clm_id, dob_dt
%do i=1 %to &nvar;
  %let var=icd_dgns_cd&i ;
  , &var
%end;
from &indata 
where 1=0
%do i=1 %to &nvar;
  %let var=icd_dgns_cd&i;
  or substr(&var,1,5) in (&icdlist)
%end;
;
quit;
%mend query;

%query
(indata=bcarclms2021
,outdata=oud
,nvars=8
,icdlist='30400' '30401' '30402' '30403' '30470' '30471' '30472' '30473'
);

NOTE that in SAS the IN operator does not care if you use spaces instead of commas between the items in the list.  Using spaces makes it much easier to deal with the list in macro code since commas might be confused as being part of the function call.

But why would you use SQL for this type of problem?  Why not just use normal SAS code?

%let icdlist='30400' '30401' '30402' '30403' '30470' '30471' '30472' '30473';
data oud;
  set bcarclms2021;
  found=0;
  array icd_dgns_cd[8];
  do i=1 to dim(icd_dgns_cd) while(not found);
    found = substr(icd_dgns_cd[i],1,5) in (&icdlist);
  end;
  if found;
  drop found i;
run;

[PaigeMiller]

@CathyVI what do you mean "it didn't work"? Saying "it didn't work" and providing no other information never is sufficient.

Did it generate an error? If so, show us the log for this PROC SQL. Are the results not what you expect? If so, explain a lot more so we can understand, and provide sample data.

[Patrick]

@CathyVI From the looks of it the solution from this discussion could also be applicable to your case.