Hi Community, I implement a macro to copy a patient details from master-table to sub-table. Patient id is a primary key in both table. Now i need to update their Gender,Date-of-birth,First-visit-date of patients by Patient ID. Proc sql;
update sub_table as a
set sex = (
select b.sex
from Master_table b
where a.P_ID = b.Patient_id and
)
where sex is null;
quit; For this query I implement a macro like %macro myUpd1(tbn, vart, varf, from);
proc sql;
update &tbn. a
set &vart. = (select &varf. from &from. where a.P_ID = patient_id)
where &vart. is null;
quit;
%mend myUpd1;
%myUpd1(sub_table, sex, sex, Master_table);
%myUpd1(sub_table, dob, dob, Master_table);
%myUpd1(sub_table, fvdate, firstvisitdate, Master_table); %macro : tbn - subtable vart - variable to (subtable var) varf - variable from (Master table var) from - Master table It gives a perfect output but for this simple query it takes 15 to 20 minutes to fetch the record from master table. The sub-table have only 2000 patient ID's. I don't know, What would be the reason behind the delay (Query / any software issue) While I try this with SQL it implement in seconds but on SAS it take much time. Even though my system is directly connected to the server. Please let me know the issue, Thanks in advance!
... View more
