Sub setting a Data Set

[pw7632]

I have my original table, I am making a new one with new criteria. Some of the ranges or values that have to be included are: 74300-74392, 92745, 12734, 3400-34098, 'Z0506-Z0600', Y1201-Y1390. i have two questions:

1. How do I extract the large numerical ranges?

2. How do I extract the ranges that have variables as the first character? 

[Reeza]

Can you provide some more details? I can make some educated guesses as to what you're trying to do, but as someone who can literally only see what you've typed here there isn't enough information to answer your questions.

[PaigeMiller]

1. Extract ranges variable — example

proc means data=have(where=(74300<=variable<=74392));
...
run;

2. Extract range, character variable — example

proc freq data=have(where=(variable>='Z0506' and variable<='Z0600'));
...
run;

[pw7632]

Thank you! That worked but I am using proc sql for my data so how would i do that in all the same code so multiple ranges can end up in the same subsetted dats set.

[Reeza]

The WHERE logic is the same in SQL or a data step.
Modify your WHERE statement. Also, this must be multiple variables I assume? A variable can only have one type, character or numeric so you only have one set of ranges.

[pw7632]

Its almost identical to that list. The column i have has values similar to those i listed. How would it look if I wanted to extract those variables I listed above?

[unison]

something like this? I'm assuming your variable (var1) is a character variable.

proc sql;
create table want as
select * from have
where
var1 between '74300' and '74392' or 
var1='92745' or 
var1='12734' or
var1 between '3400' and '34098' or
var1 between 'Z0506' and 'Z0600' or 
var1 between 'Y1201' and 'Y1390'
;
quit;

I'd probably use proc format for this though in case your logic changes or is repeated throughout your code.

[pw7632]

I changed all of the values to numeric, would that change anything? 

[pw7632]

I ran it and got these errors:

Expression using BETWEEN has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: The following columns were not found in the contributing tables: Z0390, Z0450,
A1409, A1500, L0001, G7500, G7600

[ballardw]

---

@pw7632 wrote:

I ran it and got these errors:

Expression using BETWEEN has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using BETWEEN has components that are of different data types.
ERROR: The following columns were not found in the contributing tables: Z0390, Z0450,
A1409, A1500, L0001, G7500, G7600

---

Please copy the code you submitted along with the error messages from log. Paste the coped text into a code box on the forum using the {I} icon to preserve formatting.

[pw7632]

proc sql;
create table Behavioral_Health as
select * from Behave_2
where
CDE_PROC_PRIM=CDE_PROC_PRIM between Z0390 and Z0450,or CDE_PROC_PRIM between A1409 and A1500 or  CDE_PROC_PRIM between G7500 and G7600
quit;

[ballardw]

---

@pw7632 wrote:

proc sql;
create table Behavioral_Health as
select * from Behave_2
where
CDE_PROC_PRIM=CDE_PROC_PRIM between Z0390 and Z0450,or CDE_PROC_PRIM between A1409 and A1500 or  CDE_PROC_PRIM between G7500 and G7600
quit;

---

 NOT pasted into a code box as requested

Text values that are not enclosed in quotes of some sort will be treated as variable names so as a minimum:

CDE_PROC_PRIM=CDE_PROC_PRIM between "Z0390" and "Z0450",or CDE_PROC_PRIM between "A1409" and "A1500" or  CDE_PROC_PRIM between "G7500" and "G7600"

 But

character values are case sensitive so if the value is "Z0390" then use upper case Z not "z0390"

and "between" is not really a good idea for ranges of character values because of the comparison rules for strings. "Z04" is going to be between Z0390 and Z0450 because character values stop comparing when reaching the end of the shorter value and since 4 comes after 3 in the comparison order then it is larger than Z03.

If all of your values have exactly 5 characters this may work but be aware for future use.

[ballardw]

If you have integer numeric values you can use the IN operator with a colon to indicate a range of integer values

where x in ( 74300:74392, 92745, 12734, 34000:34098)

if you want all values for a character variable that start with a specific character then something like

where substring(var,1,1)='Z'