topic Re: remove value that contain 21 in SAS Programming

remove value that contain 21

Jahanzaib — Wed, 24 May 2017 08:20:38 GMT

I have a dataset which has more than 10 million observations. In this, I have variable name NAICS it has different values. I want to remove all those values that start with 21. It contains different no of digits like 21, 2145, 210454 etc. So My intention is to remove all values with start with 21. There are some values of NAICS variable which end with 21 but I don't want them to be affected. Thanking in anticipation.

Re: remove value that contain 21

novinosrin — Wed, 24 May 2017 08:33:11 GMT

data want;

set have;

if NAICS =:'21' then delete;

run;

Assuming that variable is a character variable

Re: remove value that contain 21

s_lassen — Wed, 24 May 2017 08:28:04 GMT

@Jahanzaib: Is your variable numeric or character? And what do you mean by "removing" a value? Do you mean setting the variable to missing, or do you mean deleting the whole observation? In your example, all the values that you want to get rid of start with the number 21, does that mean that you do not want to get rid of values like 18212 or 321?

Re: remove value that contain 21

PeterClemmensen — Wed, 24 May 2017 08:28:34 GMT

data have;
input NAICS;
datalines;
21234
2134664
42355
235353
214356
;

data want;
	set have;
	if substr(left(NAICS),1,2) NE '21';
run;

Re: remove value that contain 21

Jahanzaib — Wed, 24 May 2017 08:34:21 GMT

@s_lassen I mean deleting whole observation. It's a character variable. Right. I don't want to delete values like 18212 or 321. delete only those which start with 21 not the others.

Re: remove value that contain 21

PeterClemmensen — Wed, 24 May 2017 08:36:59 GMT

I assumed a numeric variable, but my solution will work for a character variable as well.

Re: remove value that contain 21

s_lassen — Wed, 24 May 2017 08:38:54 GMT

@Jahanzaib: Then the fastest and simplest is probably using a WHERE clause:

data want;
  set have;
  where NAICS not like '21%';
run;

- this assuming that the values are left aligned, otherwise use

where left(NAICS) not like '21%';

Re: remove value that contain 21

RW9 — Wed, 24 May 2017 08:51:19 GMT

I would really advise you to try both methods on the 10mill records as I suspect this (from @s_lassen😞

data want;
  set have (where=(substr(naics,1,2) ne '21'));
run;

Would be faster than the given solution from @PeterClemmensen:

data want;
  set have;
  if substr(left(naics),1,2) ne '21';
run;

I can't prove this offhand, but vaguely remember something aboutthe set reading in the data for each row and then outputting on the if, where the where clause restricts what is coming in, so slightly earlier in the process. If so then this fractional saving would add up of millions of records. But do test.

Re: remove value that contain 21

Jahanzaib — Wed, 24 May 2017 09:00:49 GMT

Thankyou @RW9 How to delete if first two digits are 90 or greater than 90?

Re: remove value that contain 21

novinosrin — Wed, 24 May 2017 09:17:44 GMT

From your responses, i assume you are not familiar with wild card operators or colon modifiers. If you have a grasping you would have chosen @s_lassen 's answer without having to deal with functions that makes SAS work more.

If you understand collating sequences , the below solution is easy

data want;

set have;

if var>=:'90' then delete;

run;

Regards,

Naveen Srinivasan

Re: remove value that contain 21

novinosrin — Wed, 24 May 2017 09:28:44 GMT

Also, the same can be applied to a where clause:

data want;

set have;

where not (var>=:'90');

run;

Re: remove value that contain 21

RW9 — Wed, 24 May 2017 09:40:48 GMT

Well 90-99 all consist of 9x, so you can just do:

data want;
  set have (where=(substr(naics,1,2) ne '21' and char(naics,1) ne '9'));
run;