DATA Step, Macro, Functions and more

SAS exports file in PC SAS but not in batch

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

SAS exports file in PC SAS but not in batch

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.


Accepted Solutions
Solution
‎10-04-2011 07:57 AM
Valued Guide
Posts: 2,177

SAS exports file in PC SAS but not in batch

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

View solution in original post


All Replies
Solution
‎10-04-2011 07:57 AM
Valued Guide
Posts: 2,177

SAS exports file in PC SAS but not in batch

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

PROC Star
Posts: 7,467

SAS exports file in PC SAS but not in batch

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

Frequent Contributor
Posts: 75

SAS exports file in PC SAS but not in batch

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?

Valued Guide
Posts: 2,177

SAS exports file in PC SAS but not in batch

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 880 views
  • 0 likes
  • 3 in conversation