Delaying to display output

[Sathish_jammy]

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!

[andreas_lds]

Posting the log with option fullstimer active could reveal the reason for the delay.

[SASKiwi]

The method you have chosen runs a sub-query for every row update. This is inherently inefficient. There are much more efficient ways to do table updates in SAS including DATA step MERGEs, UPDATEs or MODIFYs.

[ErikLund_Jensen]

Hi Sathis_jammy

Yout problem is the 6000 sub-selects needed to update your 2000 records in the sub-table. You could optimize by updating all variables at the same time, like 

set &vart1, &vart2, &vart3 = (select &varf1, &varf2, &varf3 from &from. where a.P_ID = patient_id)

But a join would be much more efficient, You cannot join "in place", but joining to a work table and then replacing the subtable will hardly be felt with such a small subtable. I suggest something like (not tested):

%macro myupd1(sub_table, master_table);
	proc sql;
		* create updated work subtable;
		create table work.new_sub_table as
			select
				&sub_table.P_ID,
				&sub_table.other_variable1,
				&sub_table.other_variable2,
				...
				&master_table.sex,
				&master_table.dob,
				&master_table.firstvisitdate as fvdate
			from &sub_table
			left join &master_table
			on &sub_table.P_ID = &master_table.P_ID;
	quit;

	* Write back to permanent subtable;
	%if &sqlrc = 0 %then %do;
		create table &sub_table as
			select * 
			from work.new_sub_table;
		quit;
	%end;
%mend;
%myUpd1(sub_table, Master_table);