SAS EG Stored Process wont update table

[tsndrct1234]

Hi SAS Community!

I'm currently working on existing stored process and I'm currently having trouble with debugging the issue we encounter on the stored process. Please bear with me since it's my first SP development and I honestly don't know much about SAS EG SP

Anyway, the SP I'm working on has a list on its UI that is being by the users. This list shows the distinct values of all the Industry Type. Now the change is to add 2 new Industry Type but the catch is that it's from a different source table (LIB1.INST and LIB2.MASTER). I updated the code on SAS EG SP and it's not working. But when I manually test the code, it updates the table that will be the source of the Industry Type. Actually it's already on production and I don't know if there's anything else I need to have it working. Can you help me? Thank you so much! 

proc sql;
create table WORKSD7.EG_RCL_INST_X_FI_LIST_temp as
select distinct case
when XXXXXX in (0, 5) then 'Commercial Bank'
when XXXXXX in (1, 2, 3, 7) then 'Thrift Bank'
when XXXXXX in (4, 8 ) then 'Rural Bank'
when XXXXXX = 57 then 'Corporation'
else 'Non-Bank'
end as INDNAME length=35, INST as FINST, (XXXXXX * 10000) + ENTITY as BKCODE
from LIB1.INST
union
select distinct case
when XXXXXX = 31 then 'Pawnshop'
when XXXXXX = 60 then 'Money Service Businesses'
else 'Non-Bank'
end as INDNAME length=35, FCONAME as FINST, (XXXXXX * 100000) + ENTITY as BKCODE
from LIB2.MASTER;
quit;

[Kurt_Bremser]

Your Stored Process Server (and therefore all STP's) runs as user sassrv. Make sure that this user can overwrite the target dataset. Check this in SAS metadata and on the operating system level.

[tsndrct1234]

Hi Kurt! Thanks for your response, just to clarify, does this mean that that's the only possible issue when running the SP?

[Kurt_Bremser]

No. There may be other issues as well. Run the STP from the web interface and look at the log.

[tsndrct1234]

Unfortunately I'm not allowed to do so. 😞 I'm already running out of options. My senior told me that that's how they test SPs before, using the SASDEMO account in SAS EG. Another angle I'm looking at is the LIB2.MASTER is from DB2 (LIB1.INST is from AS400). They told me it has something to do with the way we extract data from DB2 and that I'm not aware of anymore. Are you familiar with that?
Thanks!

[andreas_lds]

Are you using sasdemo to start the EG-session?

[tsndrct1234]

Yes. I'm using sasdemo user only. I'm not allowed to use other user account.

[andreas_lds]

Fine. So what's the exact error message you get?

[jklaverstijn]

Argh! I banned the sasdemo ages ago. Whenever doing a SAS installation I deselect the creation of that account. Once there it usually stays forever. I am not a fan (understatement) of non-personal accounts for tasks like querying data and doing stuff on systems in general. It usually points at lazy admins or the lack of strategic vision.

[Kurt_Bremser]

IIRC, sasdemo is an internal user which cannot be used anyway for a non-pooled workspace server.

[Kurt_Bremser]

A test means to run code in its final destination and setup, everything else is useless bogus. If your code is intended to be used in the STP server environment and from the interfaces SAS provides for this, you have to test exactly that, period. If your senior demands else, then that's a clear sign that your senior is a clueless noob.

See Maxim 36.

[jklaverstijn]

As @Kurt_Bremser pointed out, the problem may very well come from the fact that the stored process server (and hence your code) runs under a generic account other than sasdemo.

You may try and register your SP for execution in a workspace server. This has some performance implications (startup is slower) but it would mean your code will run under the sasdemo account. Some limitations apply due to the way (graphics) results are produced in a WSS. So it depends on your code if this is an option.

There are many other reasons for your code to fail in the STP server but this authorization issue is by far the most common one. But only the full log will tell.

Hope this helps,

- Jan.

[tsndrct1234]

Really appreciate your feedback guys. Just to clarify the issue, I don't know if you'll be able to visualize it, but when I right click on the SP and hit the RUN <Stored Process>, the list prompt on the interface is NOT YET updated. I cannot proceed with using the SP because the interface list prompt is not updated. The code I provided above is the piece of code that re-creates the data source for the list prompt.

[Kurt_Bremser · Accepted solution]

You cannot expect a stored process to update its own parameters. You need to create a chain of STPs where the first updates the dataset and then calls the second.

[tsndrct1234]

Thanks @Kurt_Bremser! As a solution, I modified an existing job to add the block of code that modifies/updates the data source of the dynamic list. Thank you so much for all of your help!