Desktop productivity for business analysts and programmers

Using a where clause to filter out numbers within a dataset

Reply
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 ?

 

thank you in advance

Super User
Super User
Posts: 9,813

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

Posted in reply to DaveLarge

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,

 

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
numberamoutcount
781454465910005
1289456984150002
4888885007
98564564445387
454888454657869
4564564445422610
   
   
want
numberamoutcount
1289456984150002
4888885007
454888454657869
4564564445422610
New Contributor
Posts: 4

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

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
numberamountcount
781454465910005
1289456984150002
4888885007
98564564445387
454888454657869
4564564445422610
   
   
want
numberamountcount
1289456984150002
4888885007
454888454657869
4564564445422610
Super User
Super User
Posts: 9,813

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

Posted in reply to DaveLarge

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
Super User
Posts: 9,813

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

Posted in reply to DaveLarge

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

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

Posted in reply to DaveLarge

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?
Ask a Question
Discussion stats
  • 7 replies
  • 119 views
  • 0 likes
  • 3 in conversation