I created the following SAS code to generate an Excel Pivot table using ODS. The code works perfectly when running in SAS or using a Batch file to run (in the Batch file, use SAS to run the code), however, when I try to let the code run automatically put it in the Microsoft Task Scheduler, it stuck in the part of creating the Pivot Table.
How could I let the Microsoft Task Scheduler to run the code automatically, could anybody help me? Thanks a lot.
%macro pivot_pcp (data=,report_name=,pt_name=,i=);
OPTIONS FORMCHAR="|----|+|---+=|-/\<>*";
ods noresults;
ods excel file="&out_path\&data..xlsx" options(sheet_name="Parameters" embedded_titles="yes" start_at="1,2");
proc print data=parameter&i noobs;
title justify=left h=2 bold "Parameters of &report_name" ;run;
ods excel options(sheet_name="Source_Data" embedded_titles="no" start_at="1,1");
proc print data=&data;run;
ods excel close;
ods tagsets.tableeditor file="&out_path\pivot_table.js"
options(update_target="F:\\weekly\\&data..xlsx"
output_type='script'
sheet_name="Source_Data"
pivot_sheet_name="Report"
pivotrow="pcp_ID,pcp_name"
pivotcol="month"
pivotdata="members"
pivot_format="light3"
pivot_layout="tabular"
pivot_grandtotal="no"
pivot_subtotal="no"
Pivot_title="&report_name"
pivotdata_fmt="#,###"
excel_save_file="F:\\weekly\\&data..xlsx"
quit="Yes");
data _null_;
file print;
title "&report_name";
put _all_;
run;
ods tagsets.tableeditor close;
x "'&out_path\pivot_table.js'";
%mend;
From the very few details given, it seems this might be due to different users having different authorisations.
Is the scheduler using the same credentials as the other runs?
Otherwise, please provide the log of the failed run.
The code you posted is just the definition of a macro. So it wouldn't actually do anything since there is no actual call to the macro.
But if it is getting stuck then perhaps you did submit something that actually calls the macro?
Why does the code have double slashs in the paths?
Does the machine where the scheduler is running the job actually have a physical F: drive? If that is a mapped drive is the drive actually mapped for the process that is running the scheduled job?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.