topic Re: Create new variable in SQL in New SAS User

Create new variable in SQL

nhev — Wed, 07 Jul 2021 20:43:32 GMT

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;

Re: HELP

PaigeMiller — Wed, 07 Jul 2021 20:51:04 GMT

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;

Re: Create new variable in SQL

Reeza — Wed, 07 Jul 2021 21:11:14 GMT

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:

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+

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;

Re: Create new variable in SQL

PaigeMiller — Wed, 07 Jul 2021 20:50:40 GMT

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;

Re: Create new variable in SQL

Reeza — Wed, 07 Jul 2021 20:55:24 GMT

Except that's very clearly a HW question.

Re: HELP

nhev — Wed, 07 Jul 2021 21:00:12 GMT

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

Re: Create new variable in SQL

nhev — Wed, 07 Jul 2021 21:04:30 GMT

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 ;

Re: Create new variable in SQL

nhev — Wed, 07 Jul 2021 21:06:03 GMT

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

Re: HELP

PaigeMiller — Wed, 07 Jul 2021 21:08:36 GMT

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.

Re: Create new variable in SQL

Reeza — Wed, 07 Jul 2021 21:08:02 GMT

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

Re: Create new variable in SQL

Reeza — Wed, 07 Jul 2021 21:09:24 GMT

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

Re: Create new variable in SQL

nhev — Wed, 07 Jul 2021 22:18:33 GMT

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;

Re: Create new variable in SQL

nhev — Wed, 07 Jul 2021 22:29:40 GMT

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