Timing problem with call execute, proc sql, &sqlobs and a %do loop

js5 — Fri, 04 Sep 2020 09:26:01 GMT

Dear community,

I am having what seems to be timing problems with the following code:

libname config xlsx "&tfl_output_path_2\config.xlsx" access=readonly;

data _null_;
	set config.graphpad;
	call execute(macro || '(input_dataset=' || dataset || ', day_bin_variable=' || day_bin_variable || ', day_bin_variable_format=' || day_bin_variable_format || ',where=' || condition ||', output=&tfl_output_path_2\' || output || additional_parameters || ')');
run;

libname config clear;

The macros causing issues look like:

%macro export_for_graphpad_fi(input_dataset=,
			subject_variable=,
			result_variable=,
			day_variable=,
			day_bin_variable=,
			day_bin_variable_format=,
			where=,
			output=);

	proc sql noprint;
		create table data_with_dose as
			select d.DOSE,
				r.*
			from &input_dataset r
				left join cohort_dose_map d
					on r.XGROUP EQ d.XGROUP
					and r.XCOHORT EQ d.XCOHORT
				where not missing(r.XCOHORT) %if %length(&where) GT 0 %then and &where;;
		select distinct XGROUP
			into :xgroup1-:xgroup10
				from &input_dataset;
	run;

	%do i = 1 %to &sqlobs;
		ods excel file="&output._%lowcase(%sysfunc(translate(&&xgroup&i, _, %str( )))).xlsx"
			options(sheet_name="data" flow="tables");

		proc report data=data_with_dose split="\" missing;
			where XGROUP EQ "&&xgroup&i";
			columns &day_bin_variable DOSE, (GM UCLGM LCLGM);
			define &day_bin_variable / " " group exclusive preloadfmt format=&day_bin_variable_format order=data;
			define DOSE / " " across;
			define GM / " " group;
			define UCLGM / " " group;
			define LCLGM / " " group;
		run;

		ods excel close;
	%end;

	proc datasets noprint;
		delete data_with_dose;
	run;

%mend export_for_graphpad_fi;

The first time I run the code after starting a new SAS session I am getting a warning stating that &sqlobs is not defined. It appears that there are some timing issues between call execute, proc sql and when the &sqlobs variable is getting defined. How can I work them around? Thanks!

ETA: if we ignore call execute it appears that using &sqlobs with %do loops is supported: https://www.mwsug.org/proceedings/2009/how/MWSUG-2009-H01.pdf, page 6.

Re: Timing problem with call execute, proc sql, &sqlobs and a %do loop

js5 — Fri, 04 Sep 2020 09:59:01 GMT

Looks like %nrstr is the solution:
https://blogs.sas.com/content/sgf/2017/08/02/call-execute-for-sas-data-driven-programming/

topic Timing problem with call execute, proc sql, &sqlobs and a %do loop in SAS Programming

Timing problem with call execute, proc sql, &sqlobs and a %do loop

Re: Timing problem with call execute, proc sql, &sqlobs and a %do loop