Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-13-2017 02:30 PM
Super User
Posts: 7,782

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

case
  when poor between 1 and 13 then 1
  when poor between 14 and 30 then 2
end as poor
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎02-13-2017 02:30 PM
Super User
Posts: 7,782

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

case
  when poor between 1 and 13 then 1
  when poor between 14 and 30 then 2
end as poor
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 319

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

Posted in reply to KurtBremser

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

Super User
Posts: 5,428

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

Change to 13 in the second when clause.
Data never sleeps
Super Contributor
Posts: 319

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

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

Super User
Posts: 19,789

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

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

Super User
Posts: 11,343

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


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.

Respected Advisor
Posts: 4,920

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

Expanding on @KurtBremser'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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 2318 views
  • 1 like
  • 6 in conversation