08-08-2012 05:18 PM
See below for my code.
There may be more than one row/entry per application. I would like to get the following result:
When I run the code below the results were multiplied because of the inner join. In other words, for each row from T1, it is multiplied by the number of rows in T2 (for that application).
Also, the results labeled SYSTEM for the row that had the DECPOS=1. But the row with DECPOS=2 had “Manual”. I would like for SAS to show SYSTEM for all rows associated with the same application.
Any thoughts or help is appreciated!
CREATE TABLE last_dcsn_sys_manual AS
WHEN (t1.DECDECISION=1001 and t1.DECPOS=1 and t2.DECDECISION=1 and t2.DECPOS=2)THEN "System"
WHEN (t1.DECDECISION=1001 and t1.DECPOS=1 and t2.DECDECISION=4 and t2.DECPOS=2)THEN "System"
END AS SYS_MANUAL
FROM decision_1 t1 INNER JOIN decision_2 t2 ON (t1.APPLICATION_ID = t2.APPLICATION_ID)
ORDER BY t1.APPLICATION_ID, t1.DECPOS;
08-08-2012 07:41 PM
It's a little unclear how your data is set up. Adding some sample data might clarify things.
If you have more than one row with the same "Application_ID" in eith t1 or t2, I don't think this approach will work. SQL is inherently difficult for logic that involves using data from more than one record. You'd probably find it much easier to use a data step.
The "multiplication" of records that you mention is exactly the correct processing for SQL.
08-09-2012 10:21 AM
There are two issues here. One is what you're trying to do. I strongly recommend you post a bit of data and a description of what your desired result is, with a description of what you want to do, as I don't think your SQL code is doing what you want.
Based on that, the experts on this forum can make suggestions to how you can use the SAS tools to accomplish it, which I believe will be pretty easy.
Then, we can walk you through how to run your code in Enterprise Guide, which is very easy.
08-21-2012 07:16 PM
Looking at data over rows is not SQL's strong point. You really need some sequential processing which in SAS means the data step. I'd rewrite the proc SQL to pull in DecDecision and DecPos from t1 and t2 (make sure to give thm different names like DecDecision2 and DecPos2) and leave the case logic out. Then I'd code another data step using the output from your proc sql. Assuming that you have your data sorted so that the DecDecision2 and Decpos2 values that would determine the sys_manual are first for every application id:
if first.application_id then do;
if DECPOS = 1 and DecDecision = 1001 then do;
if (DecDecision2 = 1 and DECPOS2 =2) or (DecDecision2 = 2 and DECPOS2 =2) or (DecDecision2 = 4 and DECPOS2 =2)