How to search for a combination of 2 variables from a spreadsheet by using a macro?

[mnsin24]

Hello there

My aim is

• to use a spreadsheet where the end user can enter the codes( a combo of two codes) he wants to search (Input rule spreadsheet attached)
• Load these codes into a macro
• Use the macro in the coding to create alerts.

/* Importing inout file*/
PROC IMPORT
OUT=RuleCodes
datafile= "\\ar-prdsas02\SAS_User\testing loading file\Input rule.xlsx"
DBMS=xlsx REPLACE;
/*Sheet="Includes Excludes";*/
GETNAMES=YES;
DATAROW=2;
RUN;

/* Testing in multiple fields and this works*/
%macro Codes;
	%global Diag_codes1;
	proc sql noprint; 
	select Test_Diag into: Diag_codes1 separated by '" "'
	from RuleCodes;
	quit;
%mend;
%Codes;

/* creating another macro for secondary_diag*/
%macro Second_Diag;
	%global Diag_codes2;
	proc sql noprint; 
	select Test_Diag2 into: Diag_codes2 separated by '" "'
	from RuleCodes;
	quit;
%mend;
%Second_Diag;


Data Report_8ae;
set base_data4;
where Princ_Diag in: ("&Diag_codes1") AND 
(
(sdiag01 in: ("&Diag_codes2") or sdiag02 in: ("&Diag_codes2")
or sdiag03 in: ("&Diag_codes2") or sdiag04 in: ("&Diag_codes2")
or sdiag05 in: ("&Diag_codes2") or sdiag06 in: ("&Diag_codes2")
or sdiag07 in: ("&Diag_codes2") or sdiag08 in: ("&Diag_codes2")
or sdiag09 in: ("&Diag_codes2") or sdiag10 in: ("&Diag_codes2")
or sdiag11 in: ("&Diag_codes2") or sdiag12 in: ("&Diag_codes2")
or sdiag13 in: ("&Diag_codes2") or sdiag14 in: ("&Diag_codes2")
or sdiag15 in: ("&Diag_codes2") or sdiag16 in: ("&Diag_codes2")
or sdiag17 in: ("&Diag_codes2") or sdiag18 in: ("&Diag_codes2")
));
Report = '8e. Includes Excludes';
run; 

I have created macro Diag_codes1 to read the first code and Diag_codes2 to read the second code.

However the results I am getting is a combination of everything that is in Diag_codes1 and Diag_codes2 together.

What I need is line by line search of combination as per screenshot below

I am searching into basedata4 (attached) where Princ_diag and Sdiag (01 to 18) would match anything that has 2G45% AND 2I60% together. Then search again for the next combination.

I also have a concatenated field called All_diag (Princ_Diag and Sdiag01 - Sdiaf18 together) to make the search easier but the script below doesn't work with that either. 

PROC IMPORT
OUT=RuleCodes
datafile= "\\ar-prdsas02\SAS_User\testing loading file\Input rule.xlsx"
DBMS=xlsx REPLACE;
/*Sheet="Includes Excludes";*/
GETNAMES=YES;
DATAROW=2;
RUN;

/* create a macro for Combined*/
%macro Reference;
	%global Ref;
	proc sql noprint; 
	select Combine into: Ref separated by '" "'
	from RuleCodes;
	quit;
%mend;
%Reference;

Data Report_8ae;
set base_data4;
where All_Diag in: ("&Ref");
Report = '8e. Includes Excludes';
run; 

I have these Diag hard coded and currently working in a script, however the user needs to add those Diag codes on a daily basis and we thought more efficient to feed these codes from a spreadsheet that then feeds into the script.  

Any assistance on how I could move forward with that would be highly appreciated.

[Tom]

If you restructure your data so each subject's diagnosis code is on a SEPARATE observation then you are just need to use an simple JOIN to figure out if any subject has any diagnosis codes that are in the groupings in your list.

Example :

data subjects;
  input id :$10. diag :$8. ;
cards;
340546 I10BF
340546 2G553
340546 2M5387
348117 Q61BS
;

data rules;
  input rule diag :$8. ;
cards;
1 I10BF
2 2G553
;

proc sql;
create table want as 
  select a.id
       , b.rule
       , max( a.diag = b.diag) as value
  from subjects a
     , rules b
  group by 1,2
;
quit;

Results:

Obs      id      rule    value

 1     340546      1       1
 2     340546      2       1
 3     348117      1       0
 4     348117      2       0

[mnsin24]

Hi Tom

Thank you for taking the time to share this solution.  Indeed changing from a wide format to a long format could work.

Can you please let me know how to tweak the code so it searches for Diag that contains a wildcard, because the user might not need to enter the exact Diag to pick up everything that starts with I10 in your example? So a.diag = b.diag might not always be an exact match.

Below I would like the pick up all ID that that contains I10.

[mnsin24 · Accepted solution]

I have tweaked the script to read
where a.diag like catt('%',b.diag,'%')
and it seems to be working.

Let me test against the real dataset and will confirm if it works.
Thanks again!

[Tom]

That can work, but you do not want the leading %.  That would not make any sense with ICD-10 diagnosis codes.

[mnsin24]

Yes True! I will remove the leading % and only keep the second one.
Thanks a lot for your help Tom!