SAS exports file in PC SAS but not in batch

[JohnT]

Hi,

I have been scheduling some SAS jobs to run in batch for a couple of months now.

One of the jobs has never worked, it's not really important, but I have no idea why it doesn't run.

My eventual solution is to fire up SAS 9.2 open up the SAS job and run it.

The following is the log in SAS 9.2 which shows the successful run of the job:

41
42 libname av 'H:\Technical\Data\Calliden\SAS\Sirius';
NOTE: Libref AV was successfully assigned as follows:
Engine: V9
Physical Name: H:\Technical\Data\Calliden\SAS\Sirius
43
44 data _null_;
45 call symput('yr', put(year(intnx('month', today(), -1) ), 4. ) );
46 run;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
　
47 %put &yr.;
2011
48
49 %let bw_dir = H:\Technical\Reinsurance\&yr\Builders Warranty\Reports & Profiles;
50 filename bw_f "&bw_dir.\av_bw_report_%sysfunc(today(), yymmddn8.).xlsx";
51
52 *banding format pulled from original report on the Risk Profiles -BW tab;
53 proc format ;
54 value band
55 0 - 50000 = 1
56 50001 - 100000 = 2
57 100001 - 150000 = 3
58 150001 - 200000 = 4
59 200001 - 300000 = 5
60 300001 - 500000 = 6
61 500001 - 750000 = 7
62 750001 - 1000000 = 8
63 1000001 - 1500000 = 9
64 1500001 - 2000000 = 10
65 2000001 - 5000000 = 11
66 5000001 - 7000000 = 12
67 other = 99
68 ;
NOTE: Format BAND has been output.
69 run;
NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.07 seconds
cpu time 0.00 seconds
　
70
71
72 %let keep_bw =
73 policy_number
74 insured_name
75 incDate
76 scheme_description
77 insurance_file_type
78 Policy_Type
79 Gen_Premium
80 Project_Contract_Value
81
82 Parties_Builder_State
83 Parties_Builder_Postcode
84 Parties_Owner_State
85 Parties_Owner_Postcode
86 Project_Site_State
87 Project_Site_Postcode
88
89 ;
90 data bw (keep = &keep_bw. band);
91 attrib &keep_bw. label = '';
92
93 *set av.polhdr_bw (keep = &keep_bw. expdate obs = 1);
94 set av.polhdr_bw (keep = &keep_bw.);
95
96
97 * where policy_type = 'Builders Warranty'
98 and expdate >= intnx('month', today(), -1, 'e')
99 ;
100 where policy_type = 'Builders Warranty';
101
102 band = put(Project_Contract_Value, band.);
103 run;
NOTE: There were 71065 observations read from the data set AV.POLHDR_BW.
WHERE policy_type='Builders Warranty';
NOTE: The data set WORK.BW has 71065 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 12.93 seconds
cpu time 2.57 seconds
　
104
105 data bw_2010_onwards
106 bw_2009_prior;
107 set bw (rename = (policy_number = Policy_Number
108 insured_name = Insured
109 incDate = cover_start_date
110 scheme_description = Scheme
111 insurance_file_type = Policy_Status
112 Policy_Type = Gen_Policy_Type
113 Gen_Premium = Gen_Premium
114 Project_Contract_Value = Project_Contract_Value
115 band = Band ) ) ;
116 format
117 Gen_Premium Project_Contract_Value
118 dollar15.2
119 ;
120
121 if cover_start_date < '01jan2010'd then output bw_2009_prior;
122 else output bw_2010_onwards;
123 run;
NOTE: There were 71065 observations read from the data set WORK.BW.
NOTE: The data set WORK.BW_2010_ONWARDS has 30265 observations and 15 variables.
NOTE: The data set WORK.BW_2009_PRIOR has 40800 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 8.56 seconds
cpu time 1.46 seconds
　
124
125
126 proc export
127 data = bw_2010_onwards
128 outfile = bw_f
129 dbms = excel replace;
130 sheet = 'BW Policies post 2010';
131 run;
NOTE: File "H:\Technical\Reinsurance\2011\Builders Warranty\Reports &
Profiles\av_bw_report_20111004.xlsx" will be created if the export process succeeds.
NOTE: "BW_Policies_post_2010" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 6.81 seconds
cpu time 4.09 seconds
　
132
133 %put &bw_dir.;
H:\Technical\Reinsurance\2011\Builders Warranty\Reports & Profiles
134
135 proc export
136 data = bw_2009_prior
137 outfile = bw_f
138 dbms = excel replace;
139 sheet = 'BW Policies prior 2010';
140 run;
NOTE: "BW_Policies_prior_2010" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 11.96 seconds
cpu time 8.71 seconds

^^

The following is the log file of the batch job

1                                                          The SAS System                             14:24 Tuesday, October 4, 2011
NOTE: Unable to open SASUSER.REGSTRY. WORK.REGSTRY will be opened instead.
NOTE: All registry changes will be lost at the end of the session.
WARNING: Unable to copy SASUSER registry to WORK registry. Because of this, you will not see registry customizations during this 
         session.
NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.
NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA. 
NOTE: SAS (r) Proprietary Software 9.2 (TS2M0) 
      Licensed to CALLIDEN - MIGRATION TIL 31DEC11, Site 10003479.
NOTE: This session is executing on the XP_PRO  platform.
NOTE: SAS initialization used:
      real time           0.20 seconds
      cpu time            0.24 seconds
      
1          LIBNAME gins   ODBC datasrc=md0sireom1;
NOTE: Libref GINS was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: md0sireom1
2          LIBNAME ginsp  ODBC datasrc=md0sirprd1;
NOTE: Libref GINSP was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: md0sirprd1
3          
4          LIBNAME Sirius ODBC datasrc=Sirius;
NOTE: Libref SIRIUS was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: Sirius
5          LIBNAME mis    ODBC datasrc=mis;
NOTE: Libref MIS was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: mis
6          
7          LIBNAME manacc ODBC datasrc=sd1manacc;
NOTE: Libref MANACC was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: sd1manacc
8          LIBNAME badwh  ODBC datasrc=sd1badwh;
NOTE: Libref BADWH was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: sd1badwh
9          LIBNAME gios   ODBC datasrc=sd1oseom;
NOTE: Libref GIOS was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: sd1oseom
10         
11         proc sql noprint;
12            select datepart(period_end_date) format = date9. into: per
13            from gios.period
14            where period_end_complete = 1
15               having max(period_id) = period_id
16            ;
NOTE: The query requires remerging summary statistics back with the original data.
17         quit;
NOTE: PROCEDURE SQL used (Total process time):
2                                                          The SAS System                             14:24 Tuesday, October 4, 2011
      real time           0.26 seconds
      cpu time            0.01 seconds
      
18         
19         %put "&per. should be close to %sysfunc(today(), date9.)";
"31AUG2011 should be close to 04OCT2011"
20         
21         libname gg1 "h:\";
NOTE: Libref GG1 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: h:\
22         libname gg2 "s:\";
NOTE: Libref GG2 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: s:\
23         libname gg3 "m:\";
NOTE: Libref GG3 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: m:\
24         
25         %inc 'H:\Technical\Portfolio Management\Reports\Monthly\Builders Warranty\av_bw_report.sas';
NOTE: Libref AV was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: H:\Technical\Data\Calliden\SAS\Sirius
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
2011
NOTE: Format BAND has been output.
NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.06 seconds
      cpu time            0.01 seconds
      

NOTE: There were 71065 observations read from the data set AV.POLHDR_BW.
      WHERE policy_type='Builders Warranty';
NOTE: The data set WORK.BW has 71065 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           13.40 seconds
      cpu time            3.10 seconds
      

NOTE: There were 71065 observations read from the data set WORK.BW.
NOTE: The data set WORK.BW_2010_ONWARDS has 30265 observations and 15 variables.
NOTE: The data set WORK.BW_2009_PRIOR has 40800 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           7.43 seconds
      cpu time            1.39 seconds
      

1                                                          The SAS System                             14:24 Tuesday, October 4, 2011
ERROR: Connect: The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively 
       by another user, or you need permission to view and write its data.
ERROR: Error in the LIBNAME statement.
3                                                          The SAS System                             14:24 Tuesday, October 4, 2011
Connection Failed.  See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.15 seconds
      cpu time            0.09 seconds
      
H:\Technical\Reinsurance\2011\Builders Warranty\Reports & Profiles
1                                                          The SAS System                             14:24 Tuesday, October 4, 2011
ERROR: Connect: The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively 
       by another user, or you need permission to view and write its data.
ERROR: Error in the LIBNAME statement.
Connection Failed.  See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds
      
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
      real time           22.34 seconds
      cpu time            5.04 seconds
      

^^

Here is the script:

net use s: \\sydfs01\SASdata
net use h: \\sydfs02\shareddata
net use m: \\Sydfs01\SASdata\Sasuserdata\SAS_Output
::allows us to run SAS without having to reference directory
path = "C:\Program Files\SAS\SASFoundation\9.2"
::sas flags used for running jobs in batch
set sas_options = -nosplash -noxwait -noxsync -noterminal -icon
::location of work folders for SAS
set work_path = -work "s:\sasusertemp\"
::sas job shortener
set sj = %%work_path%% %%sas_options%% -sysin

::list of jobs to run
sas %sj% "H:\Technical\Data\Agencies\MIS\New Sirius\SAS Prog\ledger.sas"
sas %sj% "H:\Technical\Data\Agencies\Sirius\SAS prog\1. Downloading Sirius Tables.sas"
sas %sj% "H:\Technical\Data\Agencies\Sirius\SAS prog\2. Download Other Base Tables.sas"
sas %sj% "H:\Technical\Data\Agencies\Sirius\SAS prog\3. Creating Base Tables.sas"
sas %sj% "H:\Technical\Data\Agencies\Sirius\SAS prog\Householder RI data.sas"
sas %sj% "H:\Technical\Portfolio Management\Reports\Monthly\Builders Warranty\av_bw_report.sas"
sas %sj% "H:\Technical\Portfolio Management\Reports\Monthly\Renewal Report\SAS prog\Renewal Report.sas"
sas %sj% "H:\Technical\Portfolio Management\Reports\Monthly\UW Performance Report\SAS prog\Step0_Setup.sas"
sas %sj% "H:\Technical\Portfolio Management\Reports\Monthly\UW Performance Report\SAS prog\Step1z_AV_UWPerformance.sas"
sas %sj% "H:\Technical\Portfolio Management\Reports\Monthly\ActuarialMIS\SAS_Prog\01. Sirius Report.sas"

^^

All the other jobs export fine (if there is such a datastep).

The only thing I can see is that the output location of the extract has an ampersand in it.  I don't know how to get around this without hard coding the filename, and I don't want to do that.

Thanks.

[Peter_C · Accepted solution]

Hi John

since it worked in PC SAS, it seems unlikely the problem

1 The SAS System 14:24 Tuesday, October 4, 2011
ERROR: Connect: The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used

was caused by that stand-alone &.

Without more information in your batch log it is difficult to pin down, except to the step (extracted from the PC SAS version)

126 proc export
127 data = bw_2010_onwards
128 outfile = bw_f
129 dbms = excel replace;
130 sheet = 'BW Policies post 2010';
131 run;
NOTE: File "H:\Technical\Reinsurance\2011\Builders Warranty\Reports &
Profiles\av_bw_report_20111004.xlsx" will be created if the export process succeeds.
NOTE: "BW_Policies_post_2010" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 6.81 seconds
cpu time 4.09 seconds

1

Are you sure that fileref BW_F has been allocated successfully in batch?

(%put &SYSFILRC ; should show 0 if OK.)

2

Is it possible that you could run that stage with -SOURCE2 -MPRINT -SYMBOLGEN added to the invocation?

3

Another possible/likely cause of failure is that the account which launches the script does not have create/write privilege in the folder

H:\Technical\Reinsurance\2011\Builders Warranty\Reports & Profiles

good luck

peter

[art297]

It would help to see the actual lines of code that are submitted at the point where the error occurs

[JohnT]

Thanks Peter.C, it should be due to the permissions.

I had a look at the sercurity of the folder and the user that runs the batch job isn't listed there, but is for the other folders where I export Excel files.

I haven't verfied this yet, but I'm sure that's it.

art297, all the code should be in the first log?  I'm assuming based on the second log that the error happens after the datastep which creates the BW_2010_ONWARDS and BW_2009_PRIOR, otherwise I don't think I know SAS well enough to provide what you've suggested would be helpful in the future?

[Peter_C]

John

in your batch script, adding

-SOURCE2 -MPRINT -SYMBOLGEN

to your script line

set sas_options = -nosplash -noxwait -noxsync -noterminal -icon

will expand the batch sas log with

• %include-ed source code
• source code generated by sas macros
• and

the value of macro variables

That covers the things that Art was asking for

peter

peter