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;
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.
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.
How do I send the log?
I tried copying and psting into this reply but the log did not paste.
Think I figured out to copy and paste the log (See below).
--------------------------------------------------------------------------------------------------------------------------------------------
1 The SAS System 08:23 Wednesday, June 26, 2013
NOTE: Copyright (c) 2002-2010 by SAS Institute Inc., Cary, NC, USA.
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.
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
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.