topic Re: SAS code creating a Excel pivot table can't be run in Microsoft Task Scheduler in ODS and Base Reporting

SAS code creating a Excel pivot table can't be run in Microsoft Task Scheduler

wbsjd — Tue, 21 Jan 2020 17:19:29 GMT

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;

Re: SAS code creating a Excel pivot table can't be run in Microsoft Task Scheduler

ChrisNZ — Fri, 14 Feb 2020 03:05:12 GMT

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.

Re: SAS code creating a Excel pivot table can't be run in Microsoft Task Scheduler

Tom — Fri, 14 Feb 2020 03:11:25 GMT

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?