New Contributor
Posts: 4

# Using a where clause to filter out numbers within a dataset

Hi all,

been asked a new way of filtering out numbers from a dataset and i'm struggling to find the correct way to do this

the ask is I have a field within a dataset with 10 numbers in that field EXP (1234567890)

we know when the second number is 8 EXP (1823456790) we don't want this in the dataset and wish to remove it  anyone got an idea ?

Super User
Posts: 9,813

## Re: Using a where clause to filter out numbers within a dataset

Per the new post guidance, post test data in the form of a dataset and what you want out at the end (and any code/logs etc.).  Do you mean the variable should have the exponential function applied?  Its possble that you just want to retain a variable and output based on that, e.g:

```data want;
set have;
retain lstvar;
if _n_=1 then lstvar=var;
else do;
if lstvar=10 and var=8 then delete;
end;  lst_var=var;
run;```

Var is your variable, and 10 and 8 are just to show a value.

New Contributor
Posts: 4

## Re: Using a where clause to filter out numbers within a dataset

Hi,

The field only holds 10 digits at most im looking to take out any results where the second digit in is 8 as below – it’s a numeric field

so if the 9th number from the right is a 8 I want to delete it from the dataset

test data below

 as is number amout count 7814544659 1000 5 1289456984 15000 2 488888 500 7 9856456444 538 7 4548884546 5786 9 4564564445 4226 10 want number amout count 1289456984 15000 2 488888 500 7 4548884546 5786 9 4564564445 4226 10
New Contributor
Posts: 4

## Re: Using a where clause to filter out numbers within a dataset

Hi,

The field only holds 10 digits at most im looking to take out any results where the second digit in is 8 as below – it’s a numeric field

so if the 9th number from the right is a 8 I want to delete it from the dataset

test data below

 as is number amount count 7814544659 1000 5 1289456984 15000 2 488888 500 7 9856456444 538 7 4548884546 5786 9 4564564445 4226 10 want number amount count 1289456984 15000 2 488888 500 7 4548884546 5786 9 4564564445 4226 10
Super User
Posts: 9,813

## Re: Using a where clause to filter out numbers within a dataset

Note how I put the test data in the first step, this is how to provide test data.

```data have;
input number amout count;
datalines;
7814544659	1000	5
1289456984	15000	2
488888	500	7
9856456444	538	7
4548884546	5786	9
4564564445	4226	10
;
run;

data want;
set have;
if char(reverse(strip(put(number,best.))),9) ne "8";
run;```
New Contributor
Posts: 4

## Re: Using a where clause to filter out numbers within a dataset

this is perfect thank you

I also have to do the same with 8 digits that are already format as Char so exactly the same I want to take out the all the results where the 7th number from right is 8 ? can you help there

thanks for your help on this its really helped out

Super User
Posts: 9,813

## Re: Using a where clause to filter out numbers within a dataset

Any string function can do this.  All you need to do is ensure it is right aligned, then take char(7) or substr(..7,1).  E.g:

```data want;
set have;
if char(reverse(strip(string)),7)="8";
run;```

I would question why you have a string of numbers (in a char?) and want to select based on one of them.  It sounds like your skipping over a block then extracting something.  Maybe consider parsing the string of numbers fully and applying grouping variables to the data to simplify grouping.

Super User
Posts: 6,918