BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NathanOch
Obsidian | Level 7

Hello All,

 

I have a stored process that I built in EG and uses 3 prompts that I coded within my PROC SQL pass-through. The stored process works flawlessly in EG, but when I run it from the stored process web application it runs every time but delivers a null dataset every time. I have a hunch it has to do with my prompts being used directly within my pass-through query, but with it technically not erroring there is no log information that helps diagnose the problem.

 

The prompts I use are being encased in single quotes to help talk to the database. Here is my pass-through code with the prompts:

 

%let new_min=%str(%')%sysfunc(inputn(&Date_Range_min,date9.),yymmdd10.)%str(%');

%let new_max=%str(%')%sysfunc(inputn(&Date_Range_max,date9.),yymmdd10.)%str(%');

%let new_Client=%str(%')&Client%str(%');

%let new_Sub_Client=%str(%')&Sub_Client%str(%');

 

PROC SQL;

CONNECT TO ODBC AS Db

(dsn="HCT PROD");

CREATE TABLE SP_OPEN_INV AS

SELECT *

FROM CONNECTION TO Db

(

select

a.hcenid as Encounter

, a.hcenptacct as Account

, case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end as Client

, case when e.hcfapasid is null then 'All' else e.hcfaid end as Sub_Client

, a.hcenorglddte as First_Loaded

, b.hcaccode as Status

, c.hcactdesc as Acct_Action

, d.hcresdesc as Acct_Result

, b.hcacbal as Current_Balance

, b.zzaccustscore as P_Score

, b.zzaccustyield as Y_Score

from hcencounter as a

inner join hcaccount as b on a.hcenid = b.hcacencntrid

inner join hcfacility as e on b.hcacfacility = e.hcfaid

left join hcactioncode as c on b.hcacactcode = c.hcactid

left join hcresultcode as d on b.hcacrescode = d.hcresid

where b.zzacoffice = 'PSB'

/*and b.hcacseqnum = 99*/

and b.hcaccode not in ('CLOSED','DNFB')

/*and b.hcacwrkflow in ('ZZPSSP')*/

and a.hcenorglddte >= &new_min

and a.hcenorglddte < &new_max

and case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end in (&new_Client)

and case when e.hcfapasid is null then 'All' else e.hcfaid end in (&new_Sub_Client)

);

DISCONNECT FROM Db;

QUIT;

 

If there are any suggestions about how to fix this issue, it would be much appreciated.

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
NathanOch
Obsidian | Level 7

I solved the issue. It was on a hunch, but I used the code %sysfunc(strip(...)) to strip away what I can only assume were trailing blanks. Thanks for the conversation with this issue.

 

Nate

View solution in original post

15 REPLIES 15
Timmy2383
Lapis Lazuli | Level 10

I don't see where you're specifying an authdomain or credentials, so I'm assuming your servers are Windows using Windows Authentication to the database?

 

What server is the stored processes set to execute on?  Could it be that the stored process in EG is executed by the workspace server (and likely your user ID) but then the stored process executed from web app is using the stored process server (running as the general servers account, not your ID)? 

NathanOch
Obsidian | Level 7

Yes, we use Windows Authentification to access the database.

 

I am using the 'Default' option in the stored process, but I'm not sure that is the issue given I am not receiving an error message restricting access to the database. It seems the access is granted, but the criteria in the query returns zero datapoints. If access was restricted, wouldn't the log show a connection error?

Timmy2383
Lapis Lazuli | Level 10
Most likely. Is this all the code in the stored process? Can you provide the log (or the relevant section of it)?
NathanOch
Obsidian | Level 7

Here is the log from EG:

 

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.4 (TS1M1)

Licensed to ARRAY SERVICES GROUP, INC., Site 70156570.

NOTE: This session is executing on the X64_SRV12 platform.

 

 

NOTE: Updated analytical products:

 

SAS/STAT 13.1

NOTE: Additional host information:

X64_SRV12 WIN 6.2.9200 Server

NOTE: SAS Initialization used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

NOTE: The autoexec file, C:\sas94\config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.

>>> SAS Macro Variables:

CLIENT=BRODST

DATE_RANGE_MAX=28Apr2016

DATE_RANGE_MAX_LABEL=Today

DATE_RANGE_MAX_REL=D0D

DATE_RANGE_MIN=01Mar2016

DATE_RANGE_MIN_LABEL=March 01, 2016

SUB_CLIENT=All

 

_APSLIST=CLIENT,SUB_CLIENT,DATE_RANGE_MAX,DATE_RANGE_MAX_LABEL,DATE_RANGE_MAX_REL,DATE_RANGE_MIN,DATE_RANGE_MIN_LABEL,_RESULT,_METAU

SER,_ODSSTYLE,_ODSSTYLESHEET,_CLIENTNAME,_ODSDEST,_GOPT_DEVICE

,_ENCODING,_ODSOPTIONS,_METAPERSON,_ARCHIVE_PATH,_METAFOLDER,_PROGRAM,_CLIENT,_USERNAME,_SECUREUSERNAME

_ARCHIVE_PATH=TEMPFILE

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

_CLIENTNAME=SAS Enterprise Guide

_ENCODING=UTF8

_GOPT_DEVICE=ACTIVEX

_METAFOLDER=/Array/Production/Enterprise Analytics/ProSource/Internal Reports/

_METAPERSON=nathan.och

_METAUSER=nathan.och@ASG

_ODSDEST=tagsets.sasreport13

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

_ODSSTYLE=HtmlBlue

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

_PROGRAM=/Array/Production/Enterprise Analytics/ProSource/Internal Reports/Open Inventory List

_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 F:\sasdata\Stored Process Code Repository\Open_Inventory_List.sas is file F:\sasdata\Stored Process Code Repository\Open_Inventory_List.sas.

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

3 +*

4 +* Stored process registered by

5 +* Enterprise Guide Stored Process Manager V6.1

6 +*

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

8 +* Stored process name: Open Inventory List

9 +*

10 +* Description: Generates a list of open inventory based on client and

11 +* the date listed with Prosource.

12 +*

13 +* Keywords: SAS.BI.Metadata.Model.Types.Keyword,

14 +* SAS.BI.Metadata.Model.Types.Keyword,

15 +* SAS.BI.Metadata.Model.Types.Keyword,

16 +* SAS.BI.Metadata.Model.Types.Keyword

17 +* ====================================================================

18 +*

19 +* Stored process prompt dictionary:

20 +* ____________________________________

21 +* CLIENT

22 +* Type: Text

23 +* Label: Client

24 +* Attr: Visible, Required

25 +* Desc: Choose which client to view pools.

26 +* ____________________________________

27 +* DATE_RANGE

28 +* Type: Date range

29 +* Label: Date_Range

30 +* Attr: Visible, Required

31 +* Desc: Select the beginning date and the ending date of the

32 +* needed information.

33 +* ____________________________________

34 +* SUB_CLIENT

35 +* Type: Text

36 +* Label: Sub_Client

37 +* Attr: Visible, Required

38 +* Desc: Select the sub-client ID to get only accounts within

The SAS System

39 +* this parameter.

40 +* ____________________________________

41 +*;

42 +

43 +

44 +*ProcessBody;

45 +

46 +%global CLIENT

47 + DATE_RANGE

48 + SUB_CLIENT;

49 +

50 +%STPBEGIN;

51 +

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

53 +

54 +

55 +/* --- Start of code for "Open Accounts". --- */

56 +%let new_min=%str(%')%sysfunc(inputn(&Date_Range_min,date9.),yymmdd10.)%str(%');

57 +%let new_max=%str(%')%sysfunc(inputn(&Date_Range_max,date9.),yymmdd10.)%str(%');

58 +%let new_Client=%str(%')&Client%str(%');

59 +%let new_Sub_Client=%str(%')&Sub_Client%str(%');

60 +

61 +PROC SQL;

62 +CONNECT TO ODBC AS Db

63 +(dsn="HCT PROD");

64 +

65 +CREATE TABLE SP_OPEN_INV AS

66 +SELECT *

67 +FROM CONNECTION TO Db

68 +(

69 +

70 +select

71 +

72 +a.hcenid as Encounter

73 +, a.hcenptacct as Account

74 +, case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end as Client

75 +, case when e.hcfapasid is null then 'All' else e.hcfaid end as Sub_Client

76 +, a.hcenorglddte as First_Loaded

The SAS System

77 +, b.hcaccode as Status

78 +, c.hcactdesc as Acct_Action

79 +, d.hcresdesc as Acct_Result

80 +, b.hcacbal as Current_Balance

81 +, b.zzaccustscore as P_Score

82 +, b.zzaccustyield as Y_Score

83 +

84 +from hcencounter as a

85 +inner join hcaccount as b on a.hcenid = b.hcacencntrid

86 +inner join hcfacility as e on b.hcacfacility = e.hcfaid

87 +left join hcactioncode as c on b.hcacactcode = c.hcactid

88 +left join hcresultcode as d on b.hcacrescode = d.hcresid

89 +where b.zzacoffice = 'PSB'

90 +/*and b.hcacseqnum = 99*/

91 +and b.hcaccode not in ('CLOSED','DNFB')

92 +/*and b.hcacwrkflow in ('ZZPSSP')*/

93 +and a.hcenorglddte >= &new_min

94 +and a.hcenorglddte < &new_max

95 +and case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end = &new_Client

96 +and case when e.hcfapasid is null then 'All' else e.hcfaid end = &new_Sub_Client

97 +

98 +);

NOTE: Table WORK.SP_OPEN_INV created, with 1364 rows and 11 columns.

99 +DISCONNECT FROM Db;

100 +QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.34 seconds

cpu time 0.01 seconds

 

101 +/* --- End of code for "Open Accounts". --- */

102 +

103 +/* --- Start of code for "Code For Open Inventory List". --- */

104 +data WORK.SP_OPEN_INV1; /*New Name*/

105 + set WORK.SP_OPEN_INV; /*From the Old Name*/

106 + if Account_Number eq 1; *no one has Account 1, so you get 0 observations;

107 + run;

The SAS System

 

NOTE: Variable Account_Number is uninitialized.

NOTE: There were 1364 observations read from the data set WORK.SP_OPEN_INV.

NOTE: The data set WORK.SP_OPEN_INV1 has 0 observations and 12 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

 

108 +data WORK.SP_OPEN_INV2 / view=WORK.SP_OPEN_INV2; /*Another New Name*/

109 + if nobs eq 0 then link OneMissingRecord;

110 + set WORK.SP_OPEN_INV nobs=nobs; /*Old Name*/

111 + output;

112 + return;

113 +OneMissingRecord:

114 + output;

115 + return;

116 + run;

NOTE: DATA STEP view saved on file WORK.SP_OPEN_INV2.

NOTE: A stored DATA STEP view cannot run under a different operating system.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

117 +

118 +

119 +/* -------------------------------------------------------------------

120 + Code generated by SAS Task

121 + Generated on: Wednesday, April 27, 2016 at 4:17:14 PM

122 + By task: List Data

123 +

124 + Input Data&colon; SASApp:WORK.SP_OPEN_INV

125 + Server: SASApp

126 + ------------------------------------------------------------------- */

127 +

128 +/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/

The SAS System

129 +/* -------------------------------------------------------------------

130 + Sort data set SASApp:WORK.SP_OPEN_INV

131 + ------------------------------------------------------------------- */

132 +

133 +PROC SQL;

134 + CREATE VIEW WORK.SORTTempTableSorted AS

135 + SELECT T.Encounter, T.Account, T.Client, T.Sub_Client, T.First_Loaded, T.Status, T.Acct_Action, T.Acct_Result, T.Current_Balance, T.P_Score, T.Y_Score

136 + FROM WORK.SP_OPEN_INV2 as T

137 +;

NOTE: SQL view WORK.SORTTEMPTABLESORTED has been defined.

138 +QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

139 +TITLE;

140 +FOOTNOTE;

141 +

142 +PROC PRINT DATA=WORK.SORTTempTableSorted

143 + NOOBS

144 + LABEL

145 + ;

146 + VAR Encounter Account Client Sub_Client First_Loaded Status Acct_Action Acct_Result Current_Balance P_Score Y_Score;

147 + format Current_Balance dollar12.2 First_Loaded yymmdd10.;

148 +RUN;

NOTE: View WORK.SP_OPEN_INV2.VIEW used (Total process time):

real time 1.32 seconds

cpu time 1.31 seconds

 

NOTE: There were 1364 observations read from the data set WORK.SP_OPEN_INV.

NOTE: There were 1364 observations read from the data set WORK.SP_OPEN_INV2.

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

NOTE: PROCEDURE PRINT used (Total process time):

real time 1.32 seconds

cpu time 1.31 seconds

 

The SAS System

 

149 +/* -------------------------------------------------------------------

150 + End of task code.

151 + ------------------------------------------------------------------- */

152 +RUN; QUIT;

153 +/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/

154 +TITLE; FOOTNOTE;

155 +

156 +

157 +

158 +/* --- End of code for "Code For Open Inventory List". --- */

159 +

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

161 +;*';*";*/;quit;

162 +%STPEND;

172 +

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

174 +

NOTE: %INCLUDE (level 1) ending.

 

Timmy2383
Lapis Lazuli | Level 10
Can you provide the log from the stored process server?
NathanOch
Obsidian | Level 7

I will try and locate the stored process logs.

NathanOch
Obsidian | Level 7

Here is the log from the stored process:

 

SAS Log

1                                                          The SAS System                             10:11 Thursday, April 28, 2016

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA. 
NOTE: SAS (r) Proprietary Software 9.4 (TS1M1) 
      Licensed to ARRAY SERVICES GROUP, INC., Site 70156570.
NOTE: This session is executing on the X64_SRV12  platform.



NOTE: Updated analytical products:
      
      SAS/STAT 13.1

NOTE: Additional host information:

 X64_SRV12 WIN 6.2.9200  Server

NOTE: SAS Initialization used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: The autoexec file, C:\sas94\config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.

>>> SAS Macro Variables:

 CLIENT=BRODST              
 DATE_RANGE_MAX=28Apr2016
 DATE_RANGE_MAX_LABEL=Today (April 28, 2016)
 DATE_RANGE_MAX_REL=D0D
 DATE_RANGE_MIN=01Mar2016
 DATE_RANGE_MIN_LABEL=March 01, 2016 (March 01, 2016)
 SUB_CLIENT=All                             
 
_APSLIST=Client,Sub_Client,Date_Range_max,Date_Range_max_label,Date_Range_max_rel,Date_Range_min,Date_Range_min_label,_srvname,_rmth
ost,_htcook,_srvport,_username,_reqmeth,_htua,_grafloc,_versio
     n,_url,_rmtaddr,_reqencoding,_program,_userlocale,_updatekey,_result,_metaperson,_metauser,_metafolder,_client,_SECUREUSERNAME
 _CLIENT=StoredProcessService 9.4; JVM 1.7.0_15; Windows Server 2012 (amd64) 6.2
 _GRAFLOC=/sasweb/graph
 _HTCOOK=7c50125b9ca3808ca80bdce3e043f213_Cluster=CD485D9C3B05ABE5552D7AD571B7E386.7c50125b9ca3808ca80bdce3e043f213_SASServer1_1
 _HTUA=Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
 _METAFOLDER=/Array/Production/Enterprise Analytics/ProSource/Internal Reports/
 _METAPERSON=nathan.och
 _METAUSER=nathan.och@ASG
 _PROGRAM=/Array/Production/Enterprise Analytics/ProSource/Internal Reports/Open Inventory List
 _REPLAY="&_URL?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=&_TMPCAT.."
 _REQENCODING=UTF-8
 _REQMETH=GET
 _RESULT=STREAM
 _RMTADDR=0:0:0:0:0:0:0:1
 _RMTHOST=0:0:0:0:0:0:0:1
 _SECUREUSERNAME=(Process)
 _SRVNAME=vs-app-sas01.asg.local
 _SRVPORT=80
 _TMPCAT=APSWORK.TCAT0000
 _UPDATEKEY=217902262
 _URL=/SASStoredProcess/do
 _USERLOCALE=en_US
 _USERNAME=nathan.och
 _VERSION=Version 9.4 (Build 505)

NOTE: %INCLUDE (level 1) file F:\sasdata\Stored Process Code Repository\Open_Inventory_List.sas is file F:\sasdata\Stored Process Code Repository\Open_Inventory_List.sas.
2         +*  Begin EG generated code (do not edit this line);
3         +*
4         +*  Stored process registered by
5         +*  Enterprise Guide Stored Process Manager V6.1
6         +*
7         +*  ====================================================================
8         +*  Stored process name: Open Inventory List
9         +*
10        +*  Description: Generates a list of open inventory based on client and
11        +*               the date listed with Prosource.
12        +*
13        +*  Keywords: SAS.BI.Metadata.Model.Types.Keyword,
14        +*            SAS.BI.Metadata.Model.Types.Keyword,
15        +*            SAS.BI.Metadata.Model.Types.Keyword,
16        +*            SAS.BI.Metadata.Model.Types.Keyword
17        +*  ====================================================================
18        +*
19        +*  Stored process prompt dictionary:
20        +*  ____________________________________
21        +*  CLIENT
22        +*       Type: Text
23        +*      Label: Client
24        +*       Attr: Visible, Required
25        +*       Desc: Choose which client to view pools.
26        +*  ____________________________________
27        +*  DATE_RANGE
28        +*       Type: Date range
29        +*      Label: Date_Range
30        +*       Attr: Visible, Required
31        +*       Desc: Select the beginning date and the ending date of the
32        +*             needed information.
33        +*  ____________________________________
34        +*  SUB_CLIENT
35        +*       Type: Text
36        +*      Label: Sub_Client
37        +*       Attr: Visible, Required
38        +*       Desc: Select the sub-client ID to get only accounts within
                                                                                          The SAS System

39        +*             this parameter.
40        +*  ____________________________________
41        +*;
42        +
43        +
44        +*ProcessBody;
45        +
46        +%global CLIENT
47        +        DATE_RANGE
48        +        SUB_CLIENT;
49        +
50        +%STPBEGIN;
51        +
52        +*  End EG generated code (do not edit this line);
53        +
54        +
55        +/* --- Start of code for "Open Accounts". --- */
56        +%let new_min=%str(%')%sysfunc(inputn(&Date_Range_min,date9.),yymmdd10.)%str(%');
57        +%let new_max=%str(%')%sysfunc(inputn(&Date_Range_max,date9.),yymmdd10.)%str(%');
58        +%let new_Client=%str(%')&Client%str(%');
59        +%let new_Sub_Client=%str(%')&Sub_Client%str(%');
60        +
61        +PROC SQL;
62        +CONNECT TO ODBC AS Db
63        +(dsn="HCT PROD");
64        +
65        +CREATE TABLE SP_OPEN_INV AS
66        +SELECT *
67        +FROM CONNECTION TO Db
68        +(
69        +
70        +select
71        +
72        +a.hcenid as Encounter
73        +, a.hcenptacct as Account
74        +, case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end as Client
75        +, case when e.hcfapasid is null then 'All' else e.hcfaid end as Sub_Client
76        +, a.hcenorglddte as First_Loaded
                                                                                          The SAS System

77        +, b.hcaccode as Status
78        +, c.hcactdesc as Acct_Action
79        +, d.hcresdesc as Acct_Result
80        +, b.hcacbal as Current_Balance
81        +, b.zzaccustscore as P_Score
82        +, b.zzaccustyield as Y_Score
83        +
84        +from            hcencounter as a
85        +inner join      hcaccount as b on a.hcenid = b.hcacencntrid
86        +inner join      hcfacility as e on b.hcacfacility = e.hcfaid
87        +left join		hcactioncode as c on b.hcacactcode = c.hcactid
88        +left join		hcresultcode as d on b.hcacrescode = d.hcresid
89        +where           b.zzacoffice = 'PSB'
90        +/*and             b.hcacseqnum = 99*/
91        +and             b.hcaccode not in ('CLOSED','DNFB')
92        +/*and				b.hcacwrkflow in ('ZZPSSP')*/
93        +and 			a.hcenorglddte >= &new_min
94        +and 			a.hcenorglddte < &new_max
95        +and				case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end = &new_Client
96        +and				case when e.hcfapasid is null then 'All' else e.hcfaid end = &new_Sub_Client
97        +
98        +);
NOTE: Table WORK.SP_OPEN_INV created, with 0 rows and 11 columns.

99        +DISCONNECT FROM Db;
100       +QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds
      

101       +/* --- End of code for "Open Accounts". --- */
102       +
103       +/* --- Start of code for "Code For Open Inventory List". --- */
104       +data WORK.SP_OPEN_INV1; /*New Name*/
105       +   set WORK.SP_OPEN_INV; /*From the Old Name*/
106       +   if Account_Number eq 1; *no one has Account 1, so you get 0 observations;
107       +   run;
                                                                                          The SAS System


NOTE: Variable Account_Number is uninitialized.
NOTE: There were 0 observations read from the data set WORK.SP_OPEN_INV.
NOTE: The data set WORK.SP_OPEN_INV1 has 0 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

108       +data WORK.SP_OPEN_INV2 / view=WORK.SP_OPEN_INV2; /*Another New Name*/
109       +   if nobs eq 0 then link OneMissingRecord;
110       +   set WORK.SP_OPEN_INV nobs=nobs; /*Old Name*/
111       +   output;
112       +   return;
113       +OneMissingRecord:
114       +   output;
115       +   return;
116       +   run;

NOTE: DATA STEP view saved on file WORK.SP_OPEN_INV2.
NOTE: A stored DATA STEP view cannot run under a different operating system.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

117       +
118       +
119       +/* -------------------------------------------------------------------
120       +   Code generated by SAS Task
121       +   Generated on: Wednesday, April 27, 2016 at 4:17:14 PM
122       +   By task: List Data
123       +
124       +   Input Data&colon; SASApp:WORK.SP_OPEN_INV
125       +   Server:  SASApp
126       +   ------------------------------------------------------------------- */
127       +
128       +/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/
                                                                                          The SAS System

129       +/* -------------------------------------------------------------------
130       +   Sort data set SASApp:WORK.SP_OPEN_INV
131       +   ------------------------------------------------------------------- */
132       +
133       +PROC SQL;
134       +	CREATE VIEW WORK.SORTTempTableSorted AS
135       +		SELECT T.Encounter, T.Account, T.Client, T.Sub_Client, T.First_Loaded, T.Status, T.Acct_Action, T.Acct_Result, T.Current_Balance, T.P_Score, T.Y_Score
136       +	FROM WORK.SP_OPEN_INV2 as T
137       +;
NOTE: SQL view WORK.SORTTEMPTABLESORTED has been defined.
138       +QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

139       +TITLE;
140       +FOOTNOTE;
141       +
142       +PROC PRINT DATA=WORK.SORTTempTableSorted
143       +	NOOBS
144       +	LABEL
145       +	;
146       +	VAR Encounter Account Client Sub_Client First_Loaded Status Acct_Action Acct_Result Current_Balance P_Score Y_Score;
147       +	format Current_Balance dollar12.2 First_Loaded yymmdd10.;
148       +RUN;

NOTE: View WORK.SP_OPEN_INV2.VIEW used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
NOTE: There were 0 observations read from the data set WORK.SP_OPEN_INV.
NOTE: There were 1 observations read from the data set WORK.SP_OPEN_INV2.
NOTE: There were 1 observations read from the data set WORK.SORTTEMPTABLESORTED.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
                                                                                          The SAS System


149       +/* -------------------------------------------------------------------
150       +   End of task code.
151       +   ------------------------------------------------------------------- */
152       +RUN; QUIT;
153       +/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/
154       +TITLE; FOOTNOTE;
NathanOch
Obsidian | Level 7

Here is the full stored process code:

 

/* --- Start of code for "Open Accounts". --- */

%let new_min=%str(%')%sysfunc(inputn(&Date_Range_min,date9.),yymmdd10.)%str(%');

%let new_max=%str(%')%sysfunc(inputn(&Date_Range_max,date9.),yymmdd10.)%str(%');

%let new_Client=%str(%')&Client%str(%');

%let new_Sub_Client=%str(%')&Sub_Client%str(%');

PROC SQL;

CONNECT TO ODBC AS Db

(dsn="HCT PROD");

CREATE TABLE SP_OPEN_INV AS

SELECT *

FROM CONNECTION TO Db

(

select

a.hcenid as Encounter

, a.hcenptacct as Account

, case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end as Client

, case when e.hcfapasid is null then 'All' else e.hcfaid end as Sub_Client

, a.hcenorglddte as First_Loaded

, b.hcaccode as Status

, c.hcactdesc as Acct_Action

, d.hcresdesc as Acct_Result

, b.hcacbal as Current_Balance

, b.zzaccustscore as P_Score

, b.zzaccustyield as Y_Score

from hcencounter as a

inner join hcaccount as b on a.hcenid = b.hcacencntrid

inner join hcfacility as e on b.hcacfacility = e.hcfaid

left join hcactioncode as c on b.hcacactcode = c.hcactid

left join hcresultcode as d on b.hcacrescode = d.hcresid

where b.zzacoffice = 'PSB'

/*and b.hcacseqnum = 99*/

and b.hcaccode not in ('CLOSED','DNFB')

/*and b.hcacwrkflow in ('ZZPSSP')*/

and a.hcenorglddte >= &new_min

and a.hcenorglddte < &new_max

and case when e.hcfapasid is null then e.hcfaid else e.hcfapasid end = &new_Client

and case when e.hcfapasid is null then 'All' else e.hcfaid end = &new_Sub_Client

);

DISCONNECT FROM Db;

QUIT;

/* --- End of code for "Open Accounts". --- */

/* --- Start of code for "Code For Open Inventory List". --- */

data WORK.SP_OPEN_INV1; /*New Name*/

set WORK.SP_OPEN_INV; /*From the Old Name*/

if Account_Number eq 1; *no one has Account 1, so you get 0 observations;

run;

data WORK.SP_OPEN_INV2 / view=WORK.SP_OPEN_INV2; /*Another New Name*/

if nobs eq 0 then link OneMissingRecord;

set WORK.SP_OPEN_INV nobs=nobs; /*Old Name*/

output;

return;

OneMissingRecord:

output;

return;

run;

 

/* -------------------------------------------------------------------

Code generated by SAS Task

Generated on: Wednesday, April 27, 2016 at 4:17:14 PM

By task: List Data

Input Data&colon; SASApp:WORK.SP_OPEN_INV

Server: SASApp

------------------------------------------------------------------- */

/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/

/* -------------------------------------------------------------------

Sort data set SASApp:WORK.SP_OPEN_INV

------------------------------------------------------------------- */

PROC SQL;

CREATE VIEW WORK.SORTTempTableSorted AS

SELECT T.Encounter, T.Account, T.Client, T.Sub_Client, T.First_Loaded, T.Status, T.Acct_Action, T.Acct_Result, T.Current_Balance, T.P_Score, T.Y_Score

FROM WORK.SP_OPEN_INV2 as T

;

QUIT;

TITLE;

FOOTNOTE;

PROC PRINT DATA=WORK.SORTTempTableSorted

NOOBS

LABEL

;

VAR Encounter Account Client Sub_Client First_Loaded Status Acct_Action Acct_Result Current_Balance P_Score Y_Score;

format Current_Balance dollar12.2 First_Loaded yymmdd10.;

RUN;

/* -------------------------------------------------------------------

End of task code.

------------------------------------------------------------------- */

RUN; QUIT;

/*%_eg_conditional_dropds(WORK.SORTTempTableSorted);*/

TITLE; FOOTNOTE;

 

 

/* --- End of code for "Code For Open Inventory List". --- */

 

Timmy2383
Lapis Lazuli | Level 10
A couple things I would do to troubleshoot:

1. Add MPRINT option to see how macro variables are resolving.
2. Include simple test code, to be run on the stored process server, to validate that you can retrieve data from the tables (maybe a simple count of the records on the table, or a data step to retrieve first 10 rows from the table).
NathanOch
Obsidian | Level 7

I guess I am confused why the stored process would work in EG, but not from the web application. Shouldn't the code ran in EG work in the web application as well?

Timmy2383
Lapis Lazuli | Level 10
Most of the time, yes. However, the stored process server runs under the service account, not your ID (unless you explicitly select "Workspace Server" as the execution server on the stored process). So there can be differences, depending on differences in access between your ID and the service ID.

Maybe an easier test would be the set the execution server to "Workspace server" on the "execution" tab, and then test the stored process in the web application again. This will ensure that it starts a workspace session under your ID instead of using the stored process server running under the service ID. If it works after this change, then I would think it's a matter of differing database access between your ID and the service ID.
NathanOch
Obsidian | Level 7

I solved the issue. It was on a hunch, but I used the code %sysfunc(strip(...)) to strip away what I can only assume were trailing blanks. Thanks for the conversation with this issue.

 

Nate

Timmy2383
Lapis Lazuli | Level 10
Great! I usually use the MPRINT and MLOGIC options in my stored processes so I can see what's going on behind the scenes in the logs when I need to debug.
NathanOch
Obsidian | Level 7

Thanks Timmy, I have not used that method in the past. I will remember those functions for future issues. I love the information SAS logs provide so any information I can get is very helpful.

 

Nate

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 2559 views
  • 0 likes
  • 3 in conversation