topic Re: CRON scripts and multiple worksheet output in SAS Enterprise Guide

CRON scripts and multiple worksheet output

PrimeDougR — Thu, 12 Oct 2017 13:39:07 GMT

I have a SAS 9.4 program that outputs an Excel workbook with 2 tabs. When I run the program it works correctly. When run by a scheduled CRON script, however, only the first tab is included.

Is there a difference in the way the SAS program runs when it is run manually vs. when it is called and run by a CRON script?

Re: CRON scripts and multiple worksheet output

Kurt_Bremser — Thu, 12 Oct 2017 13:51:23 GMT

Please post your code. You can use datasets from sashelp (class, cars etc) for example sources.

Re: CRON scripts and multiple worksheet output

LinusH — Thu, 12 Oct 2017 14:48:07 GMT

And even more important, post your logs.

Re: CRON scripts and multiple worksheet output

PrimeDougR — Thu, 12 Oct 2017 16:21:03 GMT

Here is the essence of my program:

libname sasprod "/sas_shared/data";

PROC SQL;

CREATE TABLE work.DataTable1 AS

Select
Field1,
Field2,
Field3,
Field4,
Field5
from sasprod.Table1
where Field1 = 'P'
and (Field2 = 'ABC' or Field2 = 'DEF'))
);

QUIT;

data _variables_;
D = date() - 1;
call symput('D',put(D,MMDDYY10.));
D_yyyymmdd = (year(D) * 10000) + (month(D) * 100) + day(D);
call symput('D_yyyymmdd',D_yyyymmdd);
file_name = "/sastmp/sas_shared/output/TestFile &D_yyyymmdd..xlsx";
call symput('file_name',trim(file_name));
run;

proc export data = work.DataTable1
   outfile="&file_name"
   dbms=xlsx
   replace;
   sheet="Detail";
run;

proc sql;
create table work.Table1Summary as
(Field2, count(Field3) as Count3, Sum(Field4) as Sum4, sum(Field5) as Sum5
from work.DataTable1
group by Field2);
run;

proc export data = work.Table1Summary
   outfile="&file_name"
   dbms=xlsx
   replace;
   sheet="Summary";
run;

Re: CRON scripts and multiple worksheet output

PrimeDougR — Fri, 13 Oct 2017 13:33:25 GMT

This question has been resolved. It actually involved SAS columns that were using "field name"n references.

When I added

options validvarname=any;

this resolved the problem. I still don't know why it worked without that option when running the SAS program, but failed when running it via a CRON script. But at this point, the fact that it now runs both ways is all that matters.

Re: CRON scripts and multiple worksheet output

Kurt_Bremser — Fri, 13 Oct 2017 16:39:03 GMT

Validvarname = any is usually set for GUI environments, where badly structured data with stupid column names coming from external sources is quite common. Typically, SAS is used in a more controlled environment, so this option is not the default.

IMHO, people coming up with such column names should be sentenced to keep at least 1LD from computers.