topic Re: Delaying to display output in SAS Programming

Delaying to display output

Sathish_jammy — Thu, 24 Jan 2019 04:41:42 GMT

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!

Re: Delaying to display output

andreas_lds — Thu, 24 Jan 2019 06:21:01 GMT

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

Re: Delaying to display output

SASKiwi — Thu, 24 Jan 2019 07:39:46 GMT

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.

Re: Delaying to display output

ErikLund_Jensen — Thu, 24 Jan 2019 08:59:08 GMT

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);