Desktop productivity for business analysts and programmers

CRON scripts and multiple worksheet output

Reply
New Contributor
Posts: 4

CRON scripts and multiple worksheet output

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?

 

Super User
Posts: 10,557

Re: CRON scripts and multiple worksheet output

Posted in reply to PrimeDougR

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 5,916

Re: CRON scripts and multiple worksheet output

Posted in reply to KurtBremser

And even more important, post your logs.

Data never sleeps
New Contributor
Posts: 4

Re: CRON scripts and multiple worksheet output

Posted in reply to PrimeDougR

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;

 

New Contributor
Posts: 4

Re: CRON scripts and multiple worksheet output

Posted in reply to PrimeDougR

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.

 

Super User
Posts: 10,557

Re: CRON scripts and multiple worksheet output

Posted in reply to PrimeDougR

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 5 replies
  • 166 views
  • 0 likes
  • 3 in conversation