PROC SQL - Creating New Column in Existing Table with conditional logic

[chandler]

I'm trying to use PROC SQL to create a new column in an existing table. I can't seem to get my "CASE", "WHEN" expression syntax to work. I'm creating a new table, from columns of two existing tables (table aliases a and b) using a LEFT JOIN. Basically, this new column is to be used as a flag, like "YES" or "NO", based on values of another column already in table b.

An example using IF,THEN statement in conventional SAS programming is:

Data Loan_Categories (keep=Category Purpose_Code Loan_Amount Loan_Terms);
Length Category $ 8 ;
set Loan_data (keep= Purpose_Code Loan_Amount Loan_Terms) ;
X=Purpose_Code;
IF X >= 2 THEN Category = "MORTGAGE";
ELSE
IF X = 1 THEN Category = "CAR";
ELSE
IF X < 1 THEN Category = "BUSINESS";
ELSE
Category = "UNKNOWN";
run;

[sbb]

Suggest you share whatever SAS PROC SQL code you have tried which doesn't work for direct/concise feedback.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search argument, this topic/post:

proc sql add new column site:sas.com

[Flip]

CASE
WHEN Purpose_Code >= 2 THEN "MORTGAGE"
WHEN Purpose_Code = 1 THEN "CAR"
WHEN Purpose_Code < 1 THEN "BUSINESS"
ELSE
"UNKNOWN" END as category

Should do it

[chandler]

Thanks for your quick reply, FLIP. I will try this, now.

[chandler]

proc sql noexec;
create table prodlib.Loan_System_PCS_xref as
select application_id, obligor, obligation
from whse.afs_w_acaps_all as a LEFT JOIN prodlib.regH_suspects_newcolumns as b
on a.application_id=b.application_id
CASE when SCAN(obligor,1) gt ' '
then SYSTEM = "AFS_Level3"
else SYSTEM = ' '
end as SYSTEM;
quit;

[sbb]

How about this suggested DOC reference? Did it help with your PROC SQL coding? What type of error are you getting with your particular environment and data conditions? It would be helpful to see your SAS log information with any errors, for an accurate diagnosis and helpful feedback.

For your consideration and use/review, I have provided a very specific DOC link - one that provides CASE coding syntax and examples.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001334113.htm

[Flip]

In addition to what Doc said, dont put the SYSTEM = in the THEN clause. The assignment is handled after the end as clause, not as part of the value generation.

[chandler]

I'm running SAS version 9.1.3 with Service Pack 4 on a Windows XP Professional platform. Here is my SAS Log below.

1981 proc sql noexec;
1982 create table prodlib.Loan_System_PCS_xref as
1983 select application_id, obligor, obligation
1984 from whse.afs_w_acaps_all as a LEFT JOIN prodlib.regH_suspects_newcolumns as b
1985 on a.application_id=b.application_id
1986 CASE when SCAN(obligor,1) gt ' '
----
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, AND,
ANSIMISS, CROSS, EQ, EQT, EXCEPT, FULL, GE, GET, GROUP, GT, GTT, HAVING, INNER, INTERSECT, JOIN, LE, LEFT, LET,
LT, LTT, NATURAL, NE, NET, NOMISS, NOT, OR, ORDER, OUTER, RIGHT, UNION, WHERE, ^, ^=, |, ||, ~, ~=.

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

1987 then SYSTEM = "AFS_Level3"
1988 else SYSTEM = ' '
1989 end as SYSTEM;
1990 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

[Flip]

Re-Read Doc's comment , then move the case into the select clause.

proc sql noexec;
create table prodlib.Loan_System_PCS_xref as
select application_id, obligor, obligation,

CASE when SCAN(obligor,1) gt ' '
ELSE
END AS ....
from whse.afs_w_acaps_all as a LEFT JOIN prodlib.regH_suspects_newcolumns as b
on a.application_id=b.application_id

[Doc_Duke]

The CASE needs to be part of the SELECT clause, not after the JOIN.

proc sql noexec;
create table prodlib.Loan_System_PCS_xref as
select application_id, obligor, obligation,
CASE when SCAN(obligor,1) gt ' '
then SYSTEM = "AFS_Level3"
else SYSTEM = ' '
end as SYSTEM
from whse.afs_w_acaps_all as a LEFT JOIN prodlib.regH_suspects_newcolumns as b
on a.application_id=b.application_id
;
quit;