BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION
7 REPLIES 7
ybz12003
Rhodochrosite | Level 12

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

LinusH
Tourmaline | Level 20
Change to 13 in the second when clause.
Data never sleeps
ybz12003
Rhodochrosite | Level 12

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

Reeza
Super User

@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
Super User

@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
Opal | Level 21

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;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 21550 views
  • 2 likes
  • 6 in conversation