Re: Stored Process runs w/o errors but output report is empty

BillB · Posted 06-25-2013 08:14 AM

I create a store process from a query in which I expected a data set to be output.

The SP ran successfully but the output was empty.

Amir · Posted 06-25-2013 08:21 AM

Hi,

Are you fairly sure something should have been picked up? If yes then share some more information about what you're trying to do and perhaps some code which might help someone come up with an answer.

See:

Regards,

Amir.

BillB · Posted 06-25-2013 08:31 AM

Amir;

1. I created a query from a data set which then output the result of that query in the form of another data set the query was based.

2. Then I right-clicked on the Workspace and created a Stored prcedure based on the process flow.

3. I ran the SP and it output what appeared to be a file named WEBOUT unknown.

andreas_lds · Posted 06-26-2013 03:51 AM

Some things to check:

Which output-type did you select when creating the stored process? Selecting "stream" causes the webout-problem.

Is the new dataset created in the work-library? afaik SP are executed in an own session.

Post the log, so we can see what happened.

BillB · Posted 06-26-2013 08:40 AM

How do I send the log?

I tried copying and psting into this reply but the log did not paste.

BillB · Posted 06-26-2013 08:47 AM

Think I figured out to copy and paste the log (See below).

--------------------------------------------------------------------------------------------------------------------------------------------

1 The SAS System 08:23 Wednesday, June 26, 2013

NOTE: SAS (r) Proprietary Software 9.3 (TS1M2)

Licensed to North Carolina Reinsurance Facility, Site 70135293.

NOTE: This session is executing on the X64_ES08R2 platform.

NOTE: Enhanced analytical products:

SAS/STAT 12.1

NOTE: SAS Initialization used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

NOTE: The autoexec file, E:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.

>>> SAS Macro Variables:

_APSLIST=_CLIENT,_RESULT,_ODSDEST,_PROGRAM,_ENCODING,_METAUSER,_ODSSTYLE,_USERNAME,_CLIENTNAME,_METAFOLDER,_METAPERSON,_ODSOPTIONS,_

GOPT_DEVICE,_ARCHIVE_PATH,_GOPT_XPIXELS,_GOPT_YPIXELS,_ODSSTYL

ESHEET,_SECUREUSERNAME

_ARCHIVE_PATH=TEMPFILE

_CLIENT=SAS Enterprise Guide; CLR 4.0.30319.296; Microsoft Windows NT 6.1.7601 Service Pack 1

_CLIENTNAME=SAS Enterprise Guide

_ENCODING=UTF8

_GOPT_DEVICE=ACTIVEX

_GOPT_XPIXELS=2000

_GOPT_YPIXELS=1000

_METAFOLDER=/User Folders/web/My Folder/

_METAPERSON=web

_METAUSER=web@NCRB2K

_ODSDEST=tagsets.sasreport13

_ODSOPTIONS=options(rolap="on") ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v93/sasgraph.exe#version=9,3")

_ODSSTYLE=HtmlBlue

_ODSSTYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css")

_PROGRAM=/User Folders/web/My Folder/ZZZ

_REPLAY="&_URL?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=&_TMPCAT.."

_RESULT=PACKAGE_TO_ARCHIVE

_SECUREUSERNAME=(Process)

_TMPCAT=APSWORK.TCAT0000

_USERNAME=(Process)

NOTE: %INCLUDE (level 1) file E:\SASDATA\SASUSER\shared\ZZZ.sas is file E:\SASDATA\SASUSER\shared\ZZZ.sas.

2 +* Begin EG generated code (do not edit this line);

3 +*

4 +* Stored process registered by

5 +* Enterprise Guide Stored Process Manager V5.1

6 +*

7 +* ====================================================================

8 +* Stored process name: ZZZ

9 +* ====================================================================

10 +*;

11 +

12 +

13 +*ProcessBody;

14 +

15 +%STPBEGIN;

16 +

17 +OPTIONS VALIDVARNAME=ANY;

18 +

19 +%macro ExtendValidMemName;

20 +

21 +%if %sysevalf(&sysver>=9.3) %then options validmemname=extend;

22 +

23 +%mend ExtendValidMemName;

24 +

25 +%ExtendValidMemName;

26 +

27 +%LET _SASSERVERNAME=%NRBQUOTE(SASApp);

28 +

29 +* End EG generated code (do not edit this line);

30 +

31 +

32 +/* --- Start of shared macro functions. --- */

33 +

34 +/* Conditionally delete set of tables or views, if they exists */

35 +/* If the member does not exist, then no action is performed */

36 +%macro _eg_conditional_dropds /parmbuff;

37 + %let num=1;

38 + /* flags to determine whether a PROC SQL step is needed */

The SAS System

39 + /* or even started yet */

40 + %let stepneeded=0;

41 + %let stepstarted=0;

42 + %let dsname=%scan(&syspbuff,&num,',()');

43 + %do %while(&dsname ne);

44 + %if %sysfunc(exist(&dsname)) %then %do;

45 + %let stepneeded=1;

46 + %if (&stepstarted eq 0) %then %do;

47 + proc sql;

48 + %let stepstarted=1;

49 + %end;

50 + drop table &dsname;

51 + %end;

52 + %if %sysfunc(exist(&dsname,view)) %then %do;

53 + %let stepneeded=1;

54 + %if (&stepstarted eq 0) %then %do;

55 + proc sql;

56 + %let stepstarted=1;

57 + %end;

58 + drop view &dsname;

59 + %end;

60 + %let num=%eval(&num+1);

61 + %let dsname=%scan(&syspbuff,&num,',()');

62 + %end;

63 + %if &stepstarted %then %do;

64 + quit;

65 + %end;

66 +%mend _eg_conditional_dropds;

67 +

68 +/* Build where clauses from stored process parameters */

69 +

70 +%macro _eg_WhereParam( COLUMN, PARM, OPERATOR, TYPE=S, MATCHALL=_ALL_VALUES_, MATCHALL_CLAUSE=1, MAX= , IS_EXPLICIT=0);

71 + %local q1 q2 sq1 sq2;

72 + %local isEmpty;

73 + %local isEqual;

74 + %let isEqual = ("%QUPCASE(&OPERATOR)" = "EQ" OR "&OPERATOR" = "=");

75 + %let isNotEqual = ("%QUPCASE(&OPERATOR)" = "NE" OR "&OPERATOR" = "<>");

76 + %let isIn = ("%QUPCASE(&OPERATOR)" = "IN");

The SAS System

77 + %let isNotIn = ("%QUPCASE(&OPERATOR)" = "NOT IN");

78 + %local isString;

79 + %let isString = (%QUPCASE(&TYPE) eq S or %QUPCASE(&TYPE) eq STRING );

80 + %if &isString %then

81 + %do;

82 + %let q1=%str(%");

83 + %let q2=%str(%");

84 + %let sq1=%str(%');

85 + %let sq2=%str(%');

86 + %end;

87 + %else %if %QUPCASE(&TYPE) eq D or %QUPCASE(&TYPE) eq DATE %then

88 + %do;

89 + %let q1=%str(%");

90 + %let q2=%str(%"d);

91 + %let sq1=%str(%');

92 + %let sq2=%str(%');

93 + %end;

94 + %else %if %QUPCASE(&TYPE) eq T or %QUPCASE(&TYPE) eq TIME %then

95 + %do;

96 + %let q1=%str(%");

97 + %let q2=%str(%"t);

98 + %let sq1=%str(%');

99 + %let sq2=%str(%');

100 + %end;

101 + %else %if %QUPCASE(&TYPE) eq DT or %QUPCASE(&TYPE) eq DATETIME %then

102 + %do;

103 + %let q1=%str(%");

104 + %let q2=%str(%"dt);

105 + %let sq1=%str(%');

106 +

107 + %let sq2=%str(%');

108 + %end;

109 + %else

110 + %do;

111 + %let q1=;

112 + %let q2=;

113 + %let sq1=;

114 + %let sq2=;

The SAS System

115 + %end;

116 +

117 + %if "&PARM" = "" %then %let PARM=&COLUMN;

118 +

119 + %local isBetween;

120 + %let isBetween = ("%QUPCASE(&OPERATOR)"="BETWEEN" or "%QUPCASE(&OPERATOR)"="NOT BETWEEN");

121 +

122 + %if "&MAX" = "" %then %do;

123 + %let MAX = &parm._MAX;

124 + %if &isBetween %then %let PARM = &parm._MIN;

125 + %end;

126 +

127 + %if not %symexist(&PARM) or (&isBetween and not %symexist(&MAX)) %then %do;

128 + %if &IS_EXPLICIT=0 %then %do;

129 + not &MATCHALL_CLAUSE

130 + %end;

131 + %else %do;

132 + not 1=1

133 + %end;

134 + %end;

135 + %else %if "%qupcase(&&&PARM)" = "%qupcase(&MATCHALL)" %then %do;

136 + %if &IS_EXPLICIT=0 %then %do;

137 + &MATCHALL_CLAUSE

138 + %end;

139 + %else %do;

140 + 1=1

141 + %end;

142 + %end;

143 + %else %if (not %symexist(&PARM._count)) or &isBetween %then %do;

144 + %let isEmpty = ("&&&PARM" = "");

145 + %if (&isEqual AND &isEmpty AND &isString) %then

146 + &COLUMN is null;

147 + %else %if (&isNotEqual AND &isEmpty AND &isString) %then

148 + &COLUMN is not null;

149 + %else %do;

150 + %if &IS_EXPLICIT=0 %then %do;

151 + &COLUMN &OPERATOR %unquote(&q1)&&&PARM%unquote(&q2)

152 + %end;

The SAS System

153 + %else %do;

154 + &COLUMN &OPERATOR %unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2))

155 + %end;

156 + %if &isBetween %then

157 + AND %unquote(&q1)&&&MAX%unquote(&q2);

158 + %end;

159 + %end;

160 + %else

161 + %do;

162 + %local emptyList;

163 + %let emptyList = %symexist(&PARM._count);

164 + %if &emptyList %then %let emptyList = &&&PARM._count = 0;

165 + %if (&emptyList) %then

166 + %do;

167 + %if (&isNotin) %then

168 + 1;

169 + %else

170 + 0;

171 + %end;

172 + %else %if (&&&PARM._count = 1) %then

173 + %do;

174 + %let isEmpty = ("&&&PARM" = "");

175 + %if (&isIn AND &isEmpty AND &isString) %then

176 + &COLUMN is null;

177 + %else %if (&isNotin AND &isEmpty AND &isString) %then

178 + &COLUMN is not null;

179 + %else %do;

180 + %if &IS_EXPLICIT=0 %then %do;

181 + &COLUMN &OPERATOR (%unquote(&q1)&&&PARM%unquote(&q2))

182 + %end;

183 + %else %do;

184 + &COLUMN &OPERATOR (%unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2)))

185 + %end;

186 + %end;

187 + %end;

188 + %else

189 + %do;

190 + %local addIsNull addIsNotNull addComma;

The SAS System

191 + %let addIsNull = %eval(0);

192 + %let addIsNotNull = %eval(0);

193 + %let addComma = %eval(0);

194 + (&COLUMN &OPERATOR (

195 + %do i=1 %to &&&PARM._count;

196 + %let isEmpty = ("&&&PARM&i" = "");

197 + %if (&isString AND &isEmpty AND (&isIn OR &isNotIn)) %then

198 + %do;

199 + %if (&isIn) %then %let addIsNull = 1;

200 + %else %let addIsNotNull = 1;

201 + %end;

202 + %else

203 + %do;

204 + %if &addComma %then %do;,%end;

205 + %if &IS_EXPLICIT=0 %then %do;

206 + %unquote(&q1)&&&PARM&i%unquote(&q2)

207 + %end;

208 + %else %do;

209 + %unquote(%nrstr(&sq1))&&&PARM&i%unquote(%nrstr(&sq2))

210 + %end;

211 + %let addComma = %eval(1);

212 + %end;

213 + %end;)

214 + %if &addIsNull %then OR &COLUMN is null;

215 + %else %if &addIsNotNull %then AND &COLUMN is not null;

216 + %do;)

217 + %end;

218 + %end;

219 + %end;

220 +%mend;

221 +/* --- End of shared macro functions. --- */

222 +

223 +/* --- Start of code for "Query Builder". --- */

224 +%_eg_conditional_dropds(WEB.SSS_qry);

NOTE: Table WEB.SSS_QRY has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.18 seconds

cpu time 0.18 seconds

The SAS System

225 +

226 +PROC SQL;

227 + CREATE TABLE WEB.SSS_qry AS

228 + SELECT t1.DesignatedCode,

229 + t1.CompanyCode,

230 + t1.PolicyNumber,

231 + t1.EffectiveDate,

232 + t1.ExpirationDate,

233 + t1.ReceiptDate,

234 + t1.AccountingDate,

235 + t1.AccountCode,

236 + t1.TransactionDate,

237 + t1.AccidentDate,

238 + t1.TransactionCode,

239 + t1.PaymentCode,

240 + t1.ClassCode,

241 + t1.CoverageCode,

242 + t1.ClaimNumber,

243 + t1.Amount,

244 + t1.IsInError

245 + FROM EDGDV.EDGE_PREMIUMLOSS t1

246 + WHERE t1.AccountingDate >= '01JAN2013'D AND t1.AccountingDate <= '30APR2013'D;

NOTE: Table WEB.SSS_QRY created, with 6949492 rows and 17 columns.

247 +QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 7:18.21

cpu time 29.76 seconds

248 +/* --- End of code for "Query Builder". --- */

249 +

250 +/* --- Start of code for "Summary Statistics". --- */

251 +/* -------------------------------------------------------------------

252 + Code generated by SAS Task

253 +

The SAS System

254 + Generated on: Tuesday, June 25, 2013 at 10:51:08 AM

255 + By task: Summary Statistics

256 +

257 + Input Data: SASApp:web.SSS_QRY

258 + Server: SASApp

259 + ------------------------------------------------------------------- */

260 +

261 +%_eg_conditional_dropds(WORK.SORTTempTableSorted,

262 + WORK.MEANSummaryStats);

263 +/* -------------------------------------------------------------------

264 + Sort data set WEB.SSS_QRY

265 + ------------------------------------------------------------------- */

266 +PROC SORT

267 + DATA=WEB.SSS_QRY(KEEP=Amount CompanyCode ClassCode)

268 + OUT=WORK.SORTTempTableSorted

269 + ;

270 + BY ClassCode;

271 +RUN;

NOTE: There were 6949492 observations read from the data set WEB.SSS_QRY.

NOTE: The data set WORK.SORTTEMPTABLESORTED has 6949492 observations and 3 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 2.60 seconds

cpu time 5.82 seconds

272 +/* -------------------------------------------------------------------

273 + Run the Means Procedure

274 + ------------------------------------------------------------------- */

275 +TITLE;

276 +TITLE1 "Summary Statistics";

277 +TITLE2 "Results";

278 +FOOTNOTE;

279 +FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";

280 +PROC MEANS DATA=WORK.SORTTempTableSorted

281 + FW=12

282 + PRINTALLTYPES

283 + CHARTYPE

The SAS System

284 + NWAY

285 +

286 + MAX

287 + SUM

288 + N ;

289 + VAR Amount;

290 + CLASS CompanyCode / ORDER=UNFORMATTED ASCENDING;

291 + BY ClassCode;

292 +

293 +OUTPUT OUT=WORK.MEANSummaryStats(LABEL="Summary Statistics for WEB.SSS_QRY")

294 +

295 + MAX()=

296 + SUM()=

297 + N()=

298 +

299 + / AUTONAME AUTOLABEL WAYS INHERIT

300 + ;

301 +RUN;

NOTE: There were 6949492 observations read from the data set WORK.SORTTEMPTABLESORTED.

NOTE: The data set WORK.MEANSUMMARYSTATS has 113 observations and 8 variables.

NOTE: PROCEDURE MEANS used (Total process time):

real time 2.07 seconds

cpu time 2.03 seconds

302 +/* -------------------------------------------------------------------

303 + End of task code.

304 + ------------------------------------------------------------------- */

305 +RUN; QUIT;

306 +%_eg_conditional_dropds(WORK.SORTTempTableSorted);

NOTE: Table WORK.SORTTEMPTABLESORTED has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

307 +TITLE; FOOTNOTE;

The SAS System

308 +

309 +/* --- End of code for "Summary Statistics". --- */

310 +

311 +* Begin EG generated code (do not edit this line);

312 +;*';*";*/;quit;

313 +%STPEND;

323 +

324 +* End EG generated code (do not edit this line);

325 +

NOTE: %INCLUDE (level 1) ending.

Cynthia_sas · Posted 07-06-2013 03:56 PM

Hi:

Your PROC SQL step is a CREATE TABLE step, so there would be no output from that. Your PROC MEANS step might or might not produce output, depending on your other settings. If you had followed the PROC MEANS with a PROC PRINT on WORK.MEANSummaryStats data set (created by OUT=), then you would have seen something. But, since you report an empty output, my guess is that you either did not choose the right output type or you possibly need to add a PROC PRINT after the PROC MEANS. Just because EG will show you the data table created from PROC MEANS does not automatically cause your stored process to create a report.

cynthia

DonH · Posted 07-14-2013 03:27 PM

Another possibility is the data that you are reporting on has no obvservations. In that case the reporting procedure will produce no output. I have a macro that I use to make sure that output is always produced. Check out my blog entry on this for the details:

http://hcsbi.blogspot.com/2011/04/this-page-intentionally-left-blank.html

Classroom Training Available!