topic Using SAS macro with substr in SAS Programming

Using SAS macro with substr

CathyVI — Thu, 19 Sep 2024 02:09:21 GMT

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;

Re: Using SAS macro with substr

Tom — Thu, 19 Sep 2024 03:01:21 GMT

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;

Re: Using SAS macro with substr

PaigeMiller — Thu, 19 Sep 2024 10:10:01 GMT

@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.

Re: Using SAS macro with substr

Patrick — Thu, 19 Sep 2024 10:39:19 GMT

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