About metallon

metallon · ‎08-01-2016

Abstract thinking. I believe in you. You can do it! This is a solution I just came up with 2 minutes ago. I basically exclude with a subquery all the rows that did match in the first join. But its a bit lame I think: .. ON COMPRESS(ik.KN_CODE) = pb.CN OR ( 'ex'||COMPRESS(ik.KN_CODE) = pb.CN AND ik.KN_CODE NOT IN (SELECT ik.KN_CODE FROM yyy ik INNER JOIN xxx pb ON COMPRESS(ik.KN_CODE) = pb.CN ) )

metallon · ‎08-01-2016

Hi, I am sorry but the DISTINCT wont work. That is the problem with providing "test" data. It is hardly ever complete. There are many more rows that make the rows truly unique. I need a prioritiested conditional join handling.

metallon · ‎08-01-2016

Hi everyone, Thanks for the replies! I try to first match without adding 'ex' that is the number one priority join codintion only then I want to join whatever could not be joined by adding 'ex' and seeing if there is a match. Your suggestion " (COMPRESS(ik.KN_CODE) = pb.CN ) OR ('ex'||COMPRESS(ik.KN_CODE) = pb.CN ) " did not improve my result. Addressing the urgend call for test data. Here it comes: left table ik 08022200 08022200 right table (pb) 08022200 ex08022200 expected result: ik pb 08022200 08022200 attained result: ik pb 08022200 08022200 08022200 ex08022200 I need a join condition that does not look at a second condition if the first one matched. ex08022200 should have never been joined with 08022200 because 08022200=08022200 worked and I dont want that the join considers the second condition i.e. 'ex'||08022200=08022200

metallon · ‎07-29-2016

Hi SAS Experts, I got a simple PROC SQL statement that does a conditional joining to another table. The problem is that the OR is not treated sequentially but more like an AS WELL AS. The data gets multiplied. But I need it as an EITHER rather than an AS WELL AS. I tried the same with a CASE WHEN but no luck. The first works fine, 5 rows but the second produces 5 rows again although it’s a clear non-match. Only the first condition actually works. proc sql ; CREATE TABLE Import AS SELECT DISTINCT X FROM Importkont ik LEFT JOIN p_import pb ON (COMPRESS(ik.KN_CODE) = pb.CN ) OR ('ex'||COMPRESS(ik.KN_CODE) = pb.CN ) ; QUIT;

metallon · ‎07-18-2016

@cynthia "Why move everything to a web browser when you might be able to have the users provide their edits from inside an Excel file?" Why use third party software to enable users to provide data input into SAS? Competitive products like Hyperion Essbase (now Oracle) have those features. The reasons might be plenty for and against using Excel as input. I will look into the Add In option. Thanks for the idea. @LinusH "After 20 years it's a bit fascinating that SAS still not offer any eeb based data entry application. " I agree. I have been using SAS for "only" 6 years and noticed that this has not been addressed while other BI/Analytics product stacks have an option.

metallon · ‎07-15-2016

Hi Cynthia, Thanks for your reply. I extra wrote about a small table of parameters so the prompting framework is not mentioned 🙂 Lets say an Excel sheet with 200 rows. The user can update/insert and delete. I want to offer the users a web-based way to edit those 200 rows and re-run an STP that does a calculation based on those rows in combination with other data. I did not see an update mode feature in WRS. It exists in EG but I need it web-based because not everyone of the users has EG installed.

metallon · ‎07-14-2016

Hi, is there a way in SAS Studio or WRS to update/insert/delete observations/cells? I habe end users who want to update small tables filled with parameters and re-run the STP. Thanks for any hints.

metallon · ‎07-12-2016

Hi Vasilij, Yes, that is a solution but I would rather configure my stored process server correctly. it basically means I can never use it unless I sort out this issue I have now. there must a be a way to set the rights for the technical STP user (app) to have access to the library. thanks for your reply!

metallon · ‎07-12-2016

Hi, if I run the code of my STP in EG the program runs without errors but embedded in a STP and via the Stored Process Web Application I can the error "ERROR: The user does not have sufficient authority to access the data set files." Why is that? The STP is set to run on the "Stored Process Server only". No specific user credentials are required. The data is not secret or specific for a particular user group. I also configured "allow execution on selected applications servers only" i.e, store source code in metadata. No result capabilities enabled (stream,package) Code: proc sql STIMER; connect to oracle as oracleadb (user=ADBS password='{SAS002}xxxxxxx' path=ADBS); create table SASxx.fact (COMPRESS=YES) as ( select * from connection to oracleadb ( select x from y ) /*connection close*/ ); disconnect from oracleadb; quit; Bye

metallon · ‎07-12-2016

"But the purpose of the menu choice is to deploy that job as a stored process. Not to insert a STP into a job. The generated source code of the job will become the source code of the stp. " Interesting! That is the insight I needed!

metallon · ‎07-11-2016

Hi SAS Experts, what could be the reason that creating a new STP is grayed out when I right click on the job -> new - > STP. Dragging and dropping a finished STP that encapuslated some logic into my job does not work either, why? Thanks for sharing any ideas Bye

metallon · ‎07-08-2016

for some reason, the error doesnt appear anymore.

metallon · ‎07-08-2016

Hi, Thanks for the reply. I got WRS 4_4. When I select 4 elements (categories) from my dataset, all alphanumeric and no measures. I cant find a way in the UI to count distinct ( categoryA) group by categoryB, categoryC, categoryD. I am reasing the giant 370p user manual but no luck so far.

metallon · ‎07-06-2016

Hello, searching the web I gathered that a count distinct is not possible using WRS. I have various clients groups that need to count disintct amongst different variables (group by) does that mean I need to create X SAS Information Maps to address all those different grouping variable requirements? Bye

metallon · ‎07-06-2016

Hi SAS Experts, I have not done much apart from logging into WRS selecting a data source, some variables and then I see the following error: java.lang.NullPointerException at com.sas.iquery.metadata.business.impl.DataItemImpl.baseItemOnResource(DataItemImpl.java:2971) at com.sas.iquery.metadata.business.impl.AbstractBusinessModel.newDataItem(AbstractBusinessModel.java:1657) at com.sas.apps.citation.model.iq.data2.AbstractDataSelectionModel._newDataItemCore(AbstractDataSelectionModel.java:2194) at com.sas.apps.citation.model.iq.data2.AbstractDataSelectionModel._newDataItem(AbstractDataSelectionModel.java:2161) at com.sas.apps.citation.model.iq.data2.AbstractDataSelectionModel.newDataItemBean(AbstractDataSelectionModel.java:2133) at com.sas.apps.citation.view.beans.report.AbstractDataSelectionForm._resolveDataItems(AbstractDataSelectionForm.java:997) at com.sas.apps.citation.view.beans.report.AbstractDataSelectionForm.loadSelectedDataItems(AbstractDataSelectionForm.java:1546) at com.sas.apps.citation.view.beans.reportbuilder.BuilderSelectDataDialogFormBean.commit(BuilderSelectDataDialogFormBean.java:352) at com.sas.apps.citation.controller.action.reportbuilder.BuilderSelectDataDialogAction.closeDialog(BuilderSelectDataDialogAction.java:149) at com.sas.apps.citation.controller.action.CitationDialogAction.doPerform(CitationDialogAction.java:176) at com.sas.apps.citation.controller.action.CitationAction._execute(CitationAction.java:188) at com.sas.apps.citation.controller.action.CitationAction.execute(CitationAction.java:164) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425) [ 97 lines suppressed ] I have not done any hiding of variables or created sums. It is just a raw view of the dataset.

Online Status	Offline
Date Last Visited	‎08-01-2016 06:01 AM

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: updating datasets online via UI

Re: updating datasets online via UI

updating datasets online via UI

Re: "ERROR: The user does not have sufficient authority to access the ...

"ERROR: The user does not have sufficient authority to access the data...

Re: SAS DI Studio: Adding STP to Job (grayed out)

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: updating datasets online via UI

Re: count distinct, group by in WRS

Re: SAS DI Studio: Adding STP to Job (grayed out)

Re: proc sql to retrieve 10m rows from Oracle does not finish

Re: Oracle Stored Procedure DBMS_OUT in SAS

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: updating datasets online via UI

Re: updating datasets online via UI

updating datasets online via UI

Re: "ERROR: The user does not have sufficient authority to access the ...

"ERROR: The user does not have sufficient authority to access the data...

Re: SAS DI Studio: Adding STP to Job (grayed out)

SAS DI Studio: Adding STP to Job (grayed out)

Re: Selecting data source and one or two variables: java.lang.NullPoin...

Re: count distinct, group by in WRS

count distinct, group by in WRS

Selecting data source and one or two variables: java.lang.NullPointerE...