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.
... View more
