topic Syntax help for Case statement in SAS Data Management

Syntax help for Case statement

lloraine — Thu, 29 Mar 2012 17:38:36 GMT

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.

Syntax help for Case statement

Hima — Thu, 29 Mar 2012 18:11:45 GMT

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

Syntax help for Case statement

lloraine — Thu, 29 Mar 2012 18:18:48 GMT

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.

Re: Syntax help for Case statement

Hima — Thu, 29 Mar 2012 18:27:00 GMT

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.

Syntax help for Case statement

lloraine — Thu, 29 Mar 2012 18:53:42 GMT

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.

Syntax help for Case statement

Hima — Thu, 29 Mar 2012 19:03:18 GMT

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;

Syntax help for Case statement

Reeza — Thu, 29 Mar 2012 19:44:48 GMT

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.

Syntax help for Case statement

lloraine — Fri, 30 Mar 2012 10:11:27 GMT

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.

Syntax help for Case statement

lloraine — Fri, 30 Mar 2012 10:35:40 GMT

Is there a forum just for enterprise Guide questions?

Syntax help for Case statement

TomKari — Wed, 13 Dec 2017 15:52:10 GMT

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.

Syntax help for Case statement

lloraine — Mon, 02 Apr 2012 10:17:05 GMT

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.

Syntax help for Case statement

TomKari — Tue, 03 Apr 2012 03:07:14 GMT

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

Syntax help for Case statement

lloraine — Tue, 03 Apr 2012 11:48:20 GMT

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)

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

Syntax help for Case statement

Hima — Tue, 03 Apr 2012 12:10:06 GMT

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

Syntax help for Case statement

Hima — Tue, 03 Apr 2012 12:29:21 GMT

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;

Syntax help for Case statement

lloraine — Tue, 03 Apr 2012 12:33:13 GMT

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.

Syntax help for Case statement

Hima — Tue, 03 Apr 2012 12:34:52 GMT

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

Syntax help for Case statement

TomKari — Tue, 03 Apr 2012 22:00:40 GMT

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

Syntax help for Case statement

lloraine — Wed, 04 Apr 2012 10:47:29 GMT

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.

Re: Syntax help for Case statement

lloraine — Wed, 04 Apr 2012 16:19:29 GMT

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?