BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lloraine
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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.

View solution in original post

20 REPLIES 20
Hima
Obsidian | Level 7

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

lloraine
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

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
Obsidian | Level 7

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
Super User

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
Calcite | Level 5

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
Calcite | Level 5

Is there a forum just for enterprise Guide questions?

TomKari
Onyx | Level 15

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
Calcite | Level 5

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
Onyx | Level 15

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:

Result1Result2Result3
111213

Tom

lloraine
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 20 replies
  • 10745 views
  • 1 like
  • 4 in conversation