topic Re: Using a where clause to filter out numbers within a dataset in SAS Enterprise Guide

Using a where clause to filter out numbers within a dataset

DaveLarge — Thu, 12 Apr 2018 15:41:30 GMT

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

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

RW9 — Thu, 12 Apr 2018 16:00:58 GMT

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.

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

Astounding — Thu, 12 Apr 2018 16:15:06 GMT

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?

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

DaveLarge — Fri, 13 Apr 2018 06:48:03 GMT

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

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

DaveLarge — Fri, 13 Apr 2018 06:50:14 GMT

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

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

RW9 — Fri, 13 Apr 2018 08:25:28 GMT

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;

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

DaveLarge — Fri, 13 Apr 2018 08:58:40 GMT

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

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

RW9 — Fri, 13 Apr 2018 09:12:30 GMT

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.