turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Multiple Numeric Comparisons

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-21-2011 07:22 PM

Hi,

I have a situation where I need to calculate a performance based on 2 criteria. Below is what I have attempted in EG - but it errored.

The 1st 2 criterias are from one table, and the last 2 are from another table. There is no common column/field between the 2 tables - so I did not join them.

If the employee meets the two conditions, s/he is considered "EFFECTIVE".

Please advise how I should fix this. Thanks much!

***************

PROC SQL NOEXEC;

4 SELECT t1.WeightedAvg_NCL,

5 t1.ApprovalRate,

6 t2.Standard_NCL,

7 t2.Standard_ApprovalRate,

8 /* DecisionEffectiveness */

9 (if WeightedAvg_NCL <= Standard_NCL and ApprovalRate >= Standard_ApprovalRate then de='effective';

_______________

22

76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT,

GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

9 ) AS

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

I have a situation where I need to calculate a performance based on 2 criteria. Below is what I have attempted in EG - but it errored.

The 1st 2 criterias are from one table, and the last 2 are from another table. There is no common column/field between the 2 tables - so I did not join them.

If the employee meets the two conditions, s/he is considered "EFFECTIVE".

Please advise how I should fix this. Thanks much!

***************

PROC SQL NOEXEC;

4 SELECT t1.WeightedAvg_NCL,

5 t1.ApprovalRate,

6 t2.Standard_NCL,

7 t2.Standard_ApprovalRate,

8 /* DecisionEffectiveness */

9 (if WeightedAvg_NCL <= Standard_NCL and ApprovalRate >= Standard_ApprovalRate then de='effective';

_______________

22

76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT,

GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

9 ) AS

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jen123

05-21-2011 07:50 PM

I don't know why line 9 of the original posting is differnt once posted. In my code to use the characters to represent "greater than or Equal to" and "less than or equal to" - I don't spell it out in my codes. I just don't know why this forum deletes them from my codes below.

Here is what I would like for SAS EG to return one of the three performance:

*if WeightedAvg_NCL Less Than or Equal to Standard_NCL and ApprovalRate Greater Than or Equal to Standard_ApprovalRate then de='effective';

*if WeightedAvg_NCL > Standard_NCL and ApprovalRate < Standard_ApprovalRate then de='ineffective';

*if WeightedAvg_NCL = Standard_NCL and ApprovalRate = Standard_ApprovalRate then de='meet'

Thanks again!

****************

3 PROC SQL NOEXEC;

4 SELECT t1.WeightedAvg_NCL,

5 t1.ApprovalRate,

6 t2.Standard_NCL,

7 t2.Standard_ApprovalRate,

8 /* Decision Effectivness */

9 (if WeightedAvg_NCL <= Standard_NCL and ApprovalRate >= Standard_ApprovalRate then de='effective';

_______________

22

76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT,

GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

9 if

__

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

10 WeightedAvg_NCL > Standard_NCL and ApprovalRate < Standard_ApprovalRate then de='ineffective';

10 ! ) AS

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Here is what I would like for SAS EG to return one of the three performance:

*if WeightedAvg_NCL Less Than or Equal to Standard_NCL and ApprovalRate Greater Than or Equal to Standard_ApprovalRate then de='effective';

*if WeightedAvg_NCL > Standard_NCL and ApprovalRate < Standard_ApprovalRate then de='ineffective';

*if WeightedAvg_NCL = Standard_NCL and ApprovalRate = Standard_ApprovalRate then de='meet'

Thanks again!

****************

3 PROC SQL NOEXEC;

4 SELECT t1.WeightedAvg_NCL,

5 t1.ApprovalRate,

6 t2.Standard_NCL,

7 t2.Standard_ApprovalRate,

8 /* Decision Effectivness */

9 (if WeightedAvg_NCL <= Standard_NCL and ApprovalRate >= Standard_ApprovalRate then de='effective';

_______________

22

76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT,

GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

9 if

__

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

10 WeightedAvg_NCL > Standard_NCL and ApprovalRate < Standard_ApprovalRate then de='ineffective';

10 ! ) AS

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jen123

05-21-2011 10:15 PM

There are no if statements in proc sql. You need to use a case statement.

case when a is true then b when c is true then d else e end as VarName

alternatively you can check multiple values of one variable as in

case a when b then c when d then e else f end as varname

means if a=b then varname=c elseif a=d then varname=e else f

case when a is true then b when c is true then d else e end as VarName

alternatively you can check multiple values of one variable as in

case a when b then c when d then e else f end as varname

means if a=b then varname=c elseif a=d then varname=e else f

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DBailey

05-23-2011 11:25 AM

You'll have another problem. JOINs in SQL perform quite differently from SETs in DATA steps. If you don't provide join specifications, SQL will combine every record in dataset one with every record in dataset two, so you'll get a combinatorial explosion.

Tom

Tom