Syntax help for Case statement

[lloraine]

I am using SAS Enterprise Guide.  I have a Data Column I want to create but not sure of the syntax.

The gist of what I want to do is:

Max(if([Line_Code]=1,[Provision_Detail],Null)

How would I do this in SAS Enterprise Guide.  What would be the correct syntax?  I tried the CASE statement but I get errors.  It doesn't seem to like MAX and NULL. 

[Hima]

Can you post some thing like what you have in the data and what you want? It would be easy to resolve.

[lloraine]

What I posted is what I want - this is the syntax in Microsoft Access.  I need to know what the syntax would be in SAS Enterprise Guide.

Max(iif([Line_Code] = 1, [Provision_Detail],Null)

I need to create a calculated column with the above only in SAS Enterprise Guide terms. 

[Hima]

I dont know MS Access but if I understand the syntax iif([Line_Code] = 1, [Provision_Detail],Null as

if Line_Code = 1 then return Provision_Detail

otherwise return null

data table1; ** Want Table **;

set table2; ** HaveTable **;

if  Line_Code = 1 then Var=Provision_Detail;

else Var= 0;

run;

I did not get the max part, can you explain that? So I can post the remaining code but the code above works fine with out max function assuming that I got MS Access syntax correct.

[lloraine]

That doesn't look like something for Enterprise Guide.

I am looking for a case statement like

MAX CASE

When Line_Order = 1 then Provision_Detail

Else " "

End

I just don't know the correct syntax for SAS Enterprise Guide

I am trying to create a calculated column

There is a CASE {Else} function in Enterprise Guide

What I am trying to do that works in Access and Cognos is:

The Provision_Detail field has multiple lines.  I am trying to get the multple lines to go across instead of down.  According to the line order (1-9).  If the line order is 1, the above code will put it in the first column.  I am going to create a calculation for each line code (1-9).  I really need to know how to do this in Enterprise Guide.  Maybe I am in the wrong forum.

[Hima]

The code has been tested in EG.

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

PROC PRINT; RUN;

PROC SQL;
CREATE TABLE TEMP AS SELECT CASE WHEN LINE_ORDER = 1 THEN PROVISION_DETAIL ELSE 0 END AS VARIABLE_NAME FROM TEST;
QUIT;

PROC SQL;
SELECT MAX(VARIABLE_NAME) FROM TEMP;
QUIT;

The above code can be done in Base SAS. Tested in EG.

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

PROC PRINT; RUN;

DATA TEMP;
SET TEST;
IF LINE_ORDER = 1 THEN VARIABLE_NAME = PROVISION_DETAIL;
ELSE VARIABLE_NAME = 0;
RUN;

PROC MEANS DATA =TEMP MAX;
VAR VARIABLE_NAME;
RUN;

[Reeza]

I think you need to do what Hima suggested and post what you have and what you want.

SAS processes data differently (line by line) than Cognos or SQL (by dataset) though you can incorporate that in.

From you single line of code what Hima has suggested should work if you are doing it via code. If you're doing it via an interface in EG ie Query Builder,  you'd need to state which one and which version you're using.

[lloraine]

The version of Enterprise Guide I am using is 4.1(4.1.0.472).  I really am not that familiar with regular SAS.  We are just learning how to use Enterprise Guide.

[lloraine]

Is there a forum just for enterprise Guide questions?

[TomKari · Accepted solution]

There is actually a forum for Enterprise Guide; it is SAS Enterprise Guide under SAS Programming and Reporting.  

The reason for some of the confusion in responses to you is that SAS has a programming language, which is used in a SAS data step. However, you are using the Query Builder in Enterprise Guide, which uses SQL instead of the data step programming language.

The following might help:

1. In the query builder, build a new Computed Column, and within that use the Expression Builder.

2. As your expression, type or use the point-and-click features to create code that looks like:

CASE WHEN Line_Code = 1 THEN Provision_Detail ELSE . END

(or you can just copy and paste it from here). Note the . (dot) after the ELSE; that represents a SAS missing value, the equivalent of a SQL NULL.

On the way out of the expression builder, give the new variable a name, say Result1.

Now do the same to create Result2 and Result3, just changing the 1 after "WHEN Line_Code = 1" to 2 and 3.

When I preview the code of my query builder, it looks like this:

PROC SQL;

CREATE TABLE SASUSER.Query_for_TEST AS SELECT TEST.Line_Code,

  TEST.Provision_Detail,

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

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

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

FROM WORK.TEST AS TEST;

QUIT;

And my test results look like this:

Line_Code	Provision_Detail	Result1	Result2	Result3
1	11	11
2	12		12
3	13			13
4	14
5	15

Of course, you can add as many result variables as you need.

Is this going in the direction you want?

Another option is to use the Data | Transpose task. It is designed to move data from a row orientation to a column orientation very easily, but it depends on whether it matches what you need.

Tom

Editor's note: This is the marked solution because it shows how to express a CASE statement in SAS Enterprise Guide.  The original poster had deeper questions, but this should help most readers of this topic.

[lloraine]

That is the direction but instead of each result going 1 step down, it should go straight across forming one row.  That is where the MAX statement comes in.  That is how it works in ACCESS and Cognos.  I am trying to get it to work in SAS because we want to convert a lot of our ACCESS to SAS. 

[TomKari]

NOW I understand what the MAX was for!

No problem. Just change each of  your equations from step 2 to

MAX(CASE WHEN Line_Code = 1 THEN Provision_Detail ELSE . END)

and make sure you don't have any results in your query except for Result1 through Resultn.

My results look like this:

Result1	Result2	Result3
11	12	13

Tom

[lloraine]

I am getting the following errors when I use

AND MAX(CASE WHEN V_MKTDEC_PROVISIONS.LINE_ORDER = 1

25        THEN V_MKTDEC_PROVISIONS.PROVISION_DETAIL

26        ELSE . END)

27       

28        ; ERROR: Result of WHEN clause 2 is not the same data type as the preceding results. ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

I am using Enterprise Guide 4.1

[Hima]

Thats because you are code doesn't have GROUP BY. When you use aggregate functions, GROUP BY must be used in the syntax. Please refer to the below link for further details.

http://www.techonthenet.com/sql/group_by.php

[lloraine]

I grouped by in Enterprise Guide but I am still getting errors.  I am beginning to think this can't be done in Enterprise Guide.