BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nhev
Fluorite | Level 6
  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;
1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
nhev
Fluorite | Level 6
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
Diamond | Level 26
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.

--
Paige Miller
Reeza
Super User

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
Fluorite | Level 6
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
Diamond | Level 26

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;
--
Paige Miller
Reeza
Super User
Except that's very clearly a HW question.
nhev
Fluorite | Level 6
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
Super User
Try mine again and please include the full code you submitted and your log.
Reeza
Super User
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
Fluorite | Level 6
You are a star Reeza it worked. Thank you very much I just replaced higher by >
nhev
Fluorite | Level 6
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 12 replies
  • 1966 views
  • 2 likes
  • 3 in conversation