Desktop productivity for business analysts and programmers

Case Statement Help

Reply
Frequent Contributor
Posts: 94

Case Statement Help

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;

Trusted Advisor
Posts: 1,052

Re: Case Statement Help

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

Frequent Contributor
Posts: 94

Re: Case Statement Help

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!

Esteemed Advisor
Posts: 7,293

Re: Case Statement Help

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

Frequent Contributor
Posts: 94

Re: Case Statement Help

Thank you for the link. 

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

Esteemed Advisor
Posts: 7,293

Re: Case Statement Help

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

Trusted Advisor
Posts: 1,052

Re: Case Statement Help

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

Occasional Contributor
Posts: 8

Re: Case Statement Help

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;

Ask a Question
Discussion stats
  • 7 replies
  • 433 views
  • 0 likes
  • 4 in conversation