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, 2011real 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\SiriusNOTE: 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, 2011ERROR: 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, 2011Connection 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 & Profiles1 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 runsas %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.
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
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
It would help to see the actual lines of code that are submitted at the point where the error occurs
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?
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
the value of macro variables
That covers the things that Art was asking for
peter
peter
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.