proc sql;
select home.RN_band* as
from home.loans_data3
case when RN_score between 0 and 200 then RN_band = '0-200';
when RN_score between 201 and 4000 then RN_band = '201-400';
when RN_score between 401 and 600 then RN_band = '401-600';
when RN_score higher 600 then RN_band = '600+';
when RN_band='errors';
quit;
You have used data step IF/THEN code in PROC SQL, which will not work. You want to use the PROC SQL syntax:
proc sql;
select home.RN_band* as
from home.loans_data3
case when RN_score between 0 and 200 then '0-200'
when RN_score between 201 and 4000 then '201-400'
when RN_score between 401 and 600 then '401-600'
when RN_score >= 600 then '601+'
else 'errors' end as rn_band;
quit;
70 71 proc sql; 72 select home.RN_band* as 73 from home.loans_data3 74 case when RN_score between 0 and 200 then '0-200'
should be
proc sql;
create table as your_table_name as
select rn_band,
case when rn_score between 0 and 200 then '0-200'
/* All the other CASE statements go here */
from home.loans_data3;
quit;
@nhev ... please in the future DO NOT show us a portion of the log. We need to see the ENTIRE log (that's 100% of the log, every single character) from your PROC SQL. Please paste the log into a code box (as I have done), by clicking on the </> icon and then pasting the log.
FYI - I've updated your subject line to be more reflective of your question and moved your code to a code block to help with legibility. Please ensure you include a descriptive relevant subject line besides HELP in your posts. And not ALL CAPS unless you're a yeller. And most importantly - if you get an error message include the error message and log in your post as well as the code.
Your CASE statement was not correct syntax wise.
proc sql;
select *,
case when RN_score between 0 and 200 then '0-200'
when RN_score between 201 and 4000 then '201-400'
when RN_score between 401 and 600 then '401-600'
when RN_score GT 600 then '600+'
else 'errors' end as RN_BAND
from home.loans_data3
;
quit;
See the documentation for examples.
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0a85s0ijz65irn1h3jtariooea5.htm
@nhev wrote:
MY ANSWER BUT GETTING ERRORS CODES ANYONE?
- create an RN_band variable for the following groups of RN_score in SQL using the case when statement:
- 0-200
- 201-400
- 401-600
- 600+
proc sql; select home.RN_band* as from home.loans_data3 case when RN_score between 0 and 200 then RN_band = '0-200'; when RN_score between 201 and 4000 then RN_band = '201-400'; when RN_score between 401 and 600 then RN_band = '401-600'; when RN_score higher 600 then RN_band = '600+'; when RN_band='errors'; quit;
Suggestion: use formats instead of creating a new variable.
proc format;
value bandf 0-200='0-200' 201-400='201-400' /* and so on */ ;
run;
/* Example */
proc summary data=have;
class rn_score;
format rn_score bandf.;
var some_numeric_variables;
output out=stats mean=;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.