Solution out of macro. It helped only when list of condition to look up was short enough just to list in the code itself.    data want; set have array x Main_Diagnosis Other_Diagnosis_1-Other_Diagnosis_24; do over list; if x=list in :("P","C", "H"......) then a=1; end; if a=1 then output; run;   With macro and loop solution because I had looong list of diseases to eliminate from the dataset that would be too tedious ... proc import datafile="…\icd.csv" out=icd dbms=csv replace; getnames=yes; run; data icda(rename=(x=types_icd y=diseases_icd)); set icd; format y $10.; defect_original=diseases_icd; if diseases_icd ne " " then y="[*]"||diseases_icd; format x $10.; icd_new=compress(types_icd,'.'); icd_original=types_icd; x="[*]"||icd_new; drop types_icd diseases_icd defect_original icd:; run; proc sql noprint; select diseases_icd into :diseases separated by '|' from icda(where=(diseases_icd ne " ")); quit; proc sql noprint; select types_icd into :types separated by '|' from icda; quit; %let x=:Other_Diagnosis_1 - :Other_Diagnosis_10; data want; set have; icd_code = catx('*','*',Main_diagnosis, of Other_Diagnosis_Code:); if prxmatch("m/&types/oi",icd_code) > 0 and prxmatch("m/&diseases/oi",icd_code) = 0; /* types with no diseases */ run;     ... View more

Your from part, as @Reeza noted, makes of course no sense at all; with regards to SQL syntax it is Vogon poetry.   This is at least syntactically correct, but I have no clue about the semantic correctness: proc sql; create table outer as select a.groupc, a.subjc, b.groupd, b.subjd, c.group7d, c.subj7d, d.group3m, d.subj3m from groupc as a full join groupd as b on a.groupc = b.groupd and a.subjc = b.subjd full join group7d as c on a.groupc = c.group7d and a.subjc = c.subj7d full join group3m as d on c.group7d = d.group3m and c.subj7d = d.subj3m where d.subj3m is null ; quit; ... View more

Studying basics of dynamic programming. Starting with this SUGI paper: http://support.sas.com/resources/papers/proceedings11/113-2011.pdf. Let me know if you suggest better resources for novice in macro like me. Thanks Ballardw. ... View more

If you do not have a many-to-many relationship between the tables, then sorting and using a data step merge would be the better solution: proc sort data=dosing; by patient medcode id; run; proc sort data=efficacy; by patient medcode id; run; data full; merge dosing efficacy ; by patient medcode id; run; Since the WARNINGs from your original code show that only the key variables patient, mecode and id are on both datasets, this will work without unintended side effects and WARNINGs. One really needs SQL only when there is a many-to-many relationship and a cartesian product has to be built. ... View more

@Cruise I believe for your latest issue the Condition doesn't work. WARNING: Apparent symbolic reference CONDITION not resolved.   For accessing tables from different folders: You can define a single libref for multiple paths/folders (concatenated library). As long as the tables in the folders have different names you then can access these tables as if they would reside in a single folder.   If your tables follow a naming convention then you can read them using the column modifier.   Code sample: libname source ('c:\temp', 'c:\test'); data want; length _intbl sourceTable $32; set source.OUT_20: indsname=_intbl; sourceTable=_intbl; run; ... View more