How to define a value in "Between" statement by using PROC SQL?

[ybz12003]

Hello, 

How to define a column with "between" condition in PROC SQL?  I got an error message from the syntax.  Please advice.  Thanks.

705 PROC SQL;
706 update work.test
707 set

729 POOR=case when 1 =< POOR < 14 then POOR=1
-
22
730 when 14 =< POOR =< 30 then POOR=2 end;
- -
22 22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING,
USER.

[Kurt_Bremser · Accepted solution]

case
  when poor between 1 and 13 then 1
  when poor between 14 and 30 then 2
end as poor

[ybz12003]

I think the first is not accurate enough, what happen if the poor=13.5?  Will the data be assign to null?

[LinusH]

Change to 13 in the second when clause.

[ybz12003]

The 1st condition is 1 =< Poor < 14 which is not including 14.   The 2nd condition is 14 =< Poor =< 30 which is including 14.  

[Reeza]

@ybz12003 Between is inclusive so you can't use it. But there's no reason you cant compound the conditions. 

Case

when x > = 4 And x < 20 then 1

when x >= 20 and x < 40 then 2

end AS new_variable

Change the intervals to what you need, this is an exmple. 

[ballardw]

---

@ybz12003 wrote:

The 1st condition is 1 =< Poor < 14 which is not including 14.   The 2nd condition is 14 =< Poor =< 30 which is including 14.  

---

Does your age have fractional values, ie 13.8? If so then you should mention that. You have not provided and example data in a useable form.

Also you should post logs with error messages into a code box using the {i} menu item that appears at the top of the entry box to maintain formating.

Note that for most purposes you can use a FORMAT to group data for analysis and display.

Consider:

proc format library=work;
value poorgrp
0 -<14 ='1'
14 - 30='2';
run;

proc print data=work.test;
   var poor;
   format poor poorgrp.;
run;

I have literally hundreds of formats like that between my different projects.

Or use other text to display some different meaning

proc format library=work;
value poorothergrp
0 -<14 ='Top'
14 - 30='Bottom';
run;

So I get a different display without changing any values for variables.

For instance I have data with ages. For some projects the analysis is based on specific ranges of ages 18-30, 31-64, 65+, or 5 and 10 year increments. I do not create additional variables but use the formatted value to create columns/rows with suitable text as column or row headings. If I use the format in a cross tab then the formatted values are used for binning.

[PGStats]

Expanding on @Kurt_Bremser's reply:

PROC SQL;
update work.test
set POOR=case
	when poor between 1 and 13 then 1
	when poor between 13 and 30 then 2 
	else . end;
quit;