Using a where clause to filter out numbers within a dataset

[DaveLarge]

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 ?

thank you in advance

[RW9]

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. 

[DaveLarge]

Hi,

Thanks for the reply

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

[DaveLarge]

Hi,

Thanks for the reply

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

[RW9]

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;

[DaveLarge]

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

[RW9]

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.

[Astounding]

This makes a difference in how you solve it.  Is EXP:

• A character field that holds 10 digits?
• A numeric field with values in the billions?