topic Re: How to define a value in "Between" statement by using PROC SQL? in SAS Procedures

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

ybz12003 — Thu, 09 Feb 2017 21:35:59 GMT

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.

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

Kurt_Bremser — Thu, 09 Feb 2017 21:42:58 GMT

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

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

ybz12003 — Thu, 09 Feb 2017 21:55:21 GMT

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

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

LinusH — Thu, 09 Feb 2017 22:01:42 GMT

Change to 13 in the second when clause.

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

ybz12003 — Thu, 09 Feb 2017 22:31:19 GMT

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

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

Reeza — Thu, 09 Feb 2017 23:05:44 GMT

@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.

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

ballardw — Thu, 09 Feb 2017 23:11:00 GMT

@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.

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

PGStats — Fri, 10 Feb 2017 04:37:51 GMT

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;