Create new variable in SQL

[nhev]

1. create an RN_band variable for the following groups of RN_score in SQL using the case when statement:
   1. 0-200
   2. 201-400
   3. 401-600
   4. 600+

MY ANSWER BUT GETTING ERRORS CODES ANYONE?

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;

[PaigeMiller]

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;

[nhev]

Thank you for your quick reply having this errors code


1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
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'
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT,
JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

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

75 when RN_score between 201 and 4000 then '201-400'
76 when RN_score between 401 and 600 then '401-600'
77 when RN_score >= 600 then '601+'
78 else 'errors' end as rn_band;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
79 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 211.15k
OS Memory 27556.00k
Timestamp 07/07/2021 08:57:43 PM
Step Count 30 Switch Count 0
Page Faults 0
Page Reclaims 20
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

80
81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
92

[PaigeMiller]

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.

[Reeza]

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.

• No assignment variable (RN_BAND=) should be included
• Missing END AS variableName portion
• Extra semicolons
• Higher is not a valid comparison operator, use GT or >
• FROM comes after the SELECT/CASE
• Refer to * for all variables, not sure what HOME.RN_BAND is referring to but it's not your calculated variable so that can all be removed.

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:

1. create an RN_band variable for the following groups of RN_score in SQL using the case when statement:
   1. 0-200
   2. 201-400
   3. 401-600
   4. 600+

MY ANSWER BUT GETTING ERRORS CODES ANYONE?

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;

---

[nhev]

Thank you Reeza sorry I am new will do next time but having an error code with your syntax

76 when RN_score higher 600 then '600+'
______
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, THEN, ^, ^=, |, ||, ~,
~=.

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

77 else 'errors' end as RN_BAND
78 from home.loans_data3
79 ;

[PaigeMiller]

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;

[Reeza]

Except that's very clearly a HW question.

[nhev]

Still not the one. Thank you but I HAVE TO USE CASE WHEN

Page Swaps 0
Voluntary Context Switches 13
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 56


74
75 /* Example */
76 proc summary data=have;
ERROR: File WORK.HAVE.DATA does not exist.
77 class rn_score;
ERROR: No data set open to look up variables.
78 format rn_score bandf.;
79 var some_numeric_variables;
ERROR: No data set open to look up variables.
80 output out=stats mean=;
81 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.STATS may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 358.28k
OS Memory 27812.00k
Timestamp 07/07/2021 09:05:03 PM
Step Count 43 Switch Count 2
Page Faults 0
Page Reclaims 131
Page Swaps 0
Voluntary Context Switches 9
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 160

82
83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
94

[Reeza]

Try mine again and please include the full code you submitted and your log.

[Reeza · Accepted solution]

I just updated it again - you had another bug I missed - HIGHER isn't a valid comparison operator, you need to use > or GT.

[nhev]

You are a star Reeza it worked. Thank you very much I just replaced higher by >

[nhev]

Here is yours but still not working

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 higher 600 then '600+'
else 'errors' end as RN_BAND
from home.loans_data3
;
quit;