Syntax help for Case statement

[Hima]

Please post the complete code and log so it would be easy to resolve.

[Hima]

If this is how you want to see your results.

PROVISION_
Line_Code	DETAIL	Result1	Result2	Result3
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
1	100	100	.	.
2	200	.	200	.
3	300	.	.	300

The code would be

DATA TEST;
INPUT Line_Code  PROVISION_DETAIL;
CARDS;
1 100
2 200
3 300
;
RUN;

PROC SQL;

SELECT TEST.Line_Code,

  TEST.Provision_Detail,

  max(CASE WHEN Line_Code = 1 THEN Provision_Detail ELSE . END) AS Result1,

  max(CASE WHEN Line_Code = 2 THEN Provision_Detail ELSE . END ) AS Result2,

  max(CASE WHEN Line_Code = 3 THEN Provision_Detail ELSE . END ) AS Result3

FROM WORK.TEST AS TEST
group by Line_Code;

QUIT;

[TomKari]

I'm not sure why you have an AND in the snippet you provided. You definitely don't need a GROUP BY. Your three expressions should be independent.

Here's the code I used to generate the data:

data work.test;

Line_Code = 1; Provision_Detail = 11; output;

Line_Code = 2; Provision_Detail = 12; output;

Line_Code = 3; Provision_Detail = 13; output;

Line_Code = 4; Provision_Detail = 14; output;

Line_Code = 5; Provision_Detail = 15; output;

run;

I used the query builder GUI to build the example I've been presenting, but here's the code that EG generates. I think if you run the code above, and then this, in a code window, you'll get the same results I did

PROC SQL;

CREATE TABLE SASUSER.QUERY_FOR_TEST_0000 AS

SELECT (MAX(CASE WHEN Line_Code = 1 THEN Provision_Detail ELSE . END)) AS Result1,

       (MAX(CASE WHEN Line_Code = 2 THEN Provision_Detail ELSE . END)) AS Result2,

       (MAX(CASE WHEN Line_Code = 3 THEN Provision_Detail ELSE . END)) AS Result3

FROM WORK.TEST AS TEST;

QUIT;

Keep at it; you're very close!

Tom

[lloraine]

The AND is the result of other filters in the code.  The Provision Detail is lines of text per group.  Each line of text creates another record.  The reason I need to take the Provision Detail and put it going across is because if it was left as is (going down) then that increases the size of the file exponentially.  Instead of each group having one record, it would have 1-15 records for each group according to how many lines of text in the Provision Detail field.  The Line_Order field counts the number of lines of text. 

[lloraine]

I am close, maybe close in finding I can't do this.  I am getting the following error in Enterprise Guide 4.1 NOTE: The query requires remerging summary statistics back with the original data. ERROR: ORACLE execute error: ORA-01652: unable to extend temp segment by 128 in tablespace TMP_01. NOTE: Table WORK.QUERY_FOR_V_MKTDEC_PROVISIONS created, with 0 rows and 13 columns. I am using the following  code to create a computed column MAX(CASE When V_MKTDEC_PROVISIONS.LINE_ORDER = 1 Then V_MKTDEC_PROVISIONS.PROVISION_DETAIL ELSE " " END) It wouldn't accept the . so I used " " and it didn't give me an error.  I am only testing on 1 record but it doesn't bring back anything.  This is a hugh table that I am trying to query.

I got it to work.  What was causing the problem was my filtering for the dates.  I removed the filter for the dates and got results and the (MAX(CASE When V_MKTDEC_PROVISIONS.LINE_ORDER = 1 Then V_MKTDEC_PROVISIONS.PROVISION_DETAIL ELSE " " END) worked perfectly.  I still need to figure out what syntax I can use for the following pseudo code:

Effective_From_Date <= todays date - I need a function for todays date.  I was using TODAY() and DATE() but it didn't like those options.

I also have

Effective_to_date is missing or Effective_to_date >= today's date - same problem - I need a function to filter the date fields.  Do I need to convert the date fields to some date format?

[TomKari]

The reason the . didn't work is because SAS uses different representations for Oracle's NULL; . for numeric variables, blank for character. This is a really old feature of the SAS language.

On the subject of your filter, dates with DBMS tables can be tricky, as different DBMS products represent them differently. DATETIME() is a SAS function that might work. If not, try posting to the "SAS Macro Facility, Data Step and SAS Language Elements" community; someone with more Oracle experience can probably help.

Do you need your filtering statements in the actual MAX(CASE(...)) statements, or can you use a filter on the "Filter Data" tab in your EG query?