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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.