BookmarkSubscribeRSS Feed
chandler
Fluorite | Level 6
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;
9 REPLIES 9
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Fluorite | Level 6
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
Fluorite | Level 6
Thanks for your quick reply, FLIP. I will try this, now.
chandler
Fluorite | Level 6
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Rhodochrosite | Level 12
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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3518 views
  • 0 likes
  • 4 in conversation