Case Statement Help

[jen123]

See below for my code.

There may be more than one row/entry per application.  I would like to get the following result:

• If the 1st entry has DecDecision = 1001 and DecPos=1, then I want SAS to look at the 2nd row/entry (for the same application).  If the 2nd entry has DecDecision=1 and DecPos=2, then stamp the application as SYSTEM.  So, for every row/entry associated with the application, another column would state SYSTEM.
• If the 1st entry has DecDecision = 1001 and DecPos=1, then I want SAS to look at the 2nd row/entry (for the same application).  If the 2nd entry has DecDecision=2 and DecPos=2, then stamp the application as SYSTEM.  So, for every row/entry associated with the application, another column would state SYSTEM.
• If the 1st entry has DecDecision = 1001 and DecPos=1, then I want SAS to look at the 2nd row/entry (for the same application).  If the 2nd entry has DecDecision=4 and DecPos=2, then stamp the application as SYSTEM.  So, for every row/entry associated with the application, another column would state SYSTEM.

ELSE "Manual"

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!

***************************************

PROC SQL;

   CREATE TABLE last_dcsn_sys_manual AS

   SELECT DISTINCT

          t1.APPLICATION_ID,

          t1.C_DECISION_NM,

          t1.DCSN_USER_NM,

          t1.DECDECISION,

          t1.DECTIME,

          t1.DECPOS,

                CASE

                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"

                ELSE "Manual"

                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;

[TomKari]

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.

Tom

[jen123]

Would you be able to give me ideas on what I should search for on this site for examples of data step?  I am unsure how to set this up.

Thanks!

[art297]

Extremely huge question.  Here is one starting point: http://www2.sas.com/proceedings/sugi30/134-30.pdf

For others, just do a Google search for: datastep sas

[jen123]

Thank you for the link. 

I am curious though.  Is there a way to do this via EG?

[art297]

Yes, you can write code in EG.  However, I am the wrong person to ask regarding EG

[TomKari]

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.

Tom

[Hetty]

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:

data outthere;

retain SYS_MANUAL;

set last_dcsn_sys_manual

by

APPLICATION_ID;

if first.application_id then do;

sys_manual='Manual';

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)

then sys_manual='SYSTEM';

end;

end;

run;