topic Re: Removing one specific value in SAS Programming

Removing one specific value

mlogan — Wed, 07 Mar 2018 15:01:57 GMT

Hi All,

I wanted to know what would be the best command to remove one specific value from a variable. For example, I want to remove the value '999999' (a value containing 6 nines) from VAR4 of the following table.

DATA Have;
INPUT ID VAR1$ VAR4$ VAR6$;
DATALINES;
101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 999999 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX
;
RUN;

Expected output table:

ID VAR1 VAR4 VAR6

101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX

Thanks,

Re: Removing one specific value

kiranv_ — Wed, 07 Mar 2018 15:23:11 GMT

couple of ways to do is to create a new dataset or use update statement

DATA Have;
INPUT ID VAR1$ VAR4$ VAR6$;
DATALINES;
101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 999999 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX
;
RUN;

data want;
set have;
if trim(var4)  = '999999' then var4 = ' ' ;
else var4 = var4;
run;

proc sql;
update have
set var4 = ' '
where trim(var4)  = '999999';

Re: Removing one specific value

RW9 — Wed, 07 Mar 2018 15:24:02 GMT

data want;
  set have;
  if index(var4,"999999") then delete;
run;

Re: Removing one specific value

Astounding — Wed, 07 Mar 2018 16:21:37 GMT

It's an easy change ... use an easy program:

data want;

set have;

if var4='999999' then var4=' ';

run;

This works as long as there are no leading blanks before the "999999". If there are any leading blanks, however, you might need to specify whether those should be changed as well.

Re: Removing one specific value

mlogan — Wed, 07 Mar 2018 16:51:17 GMT

Hi RW9,
Your index function works, but it is removing '99999999' instead of '999999' also it is working for Numeric variable. do you know how I should modify your code if it is a Character value?

Thanks,

Re: Removing one specific value

RW9 — Wed, 07 Mar 2018 18:12:02 GMT

First, your test data shows var4 as a character variable, hence why I showed this example. Index searches for a string within another one, e.g. "9999999" contains "999999". If you apply this to numbers then the function will implicitly convert the number to character. If its just to remove that one data item, why not:

data want;
  set have;
  if strip(var4)="999999" then var4="";
run;

Re: Removing one specific value

mlogan — Wed, 07 Mar 2018 18:35:20 GMT

Hi RW9,
Would you please test if this is code is working at your end. It's not working at my end. May be I am missing something. I want to get rid of a value where there is 6 9s only, NOT 99999999.

DATA Have;
INPUT ID VAR1$ VAR$ VAR6$;
DATALINES;
101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 999999 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX
;
RUN;

data want;
set have;
if strip(var4)="999999" then var4="";
run;

Thanks,

Re: Removing one specific value

SuryaKiran — Wed, 07 Mar 2018 18:42:03 GMT

DATA WANT;
SET HAVE;
VAR4=IFC(strip(var4)="999999"," ",var4);
run;

Re: Removing one specific value

mlogan — Wed, 07 Mar 2018 20:50:37 GMT

Hi SuryaKiran,
Is the code working for you?

Re: Removing one specific value

ballardw — Wed, 07 Mar 2018 21:00:08 GMT

@mlogan wrote:
Hi RW9,
Would you please test if this is code is working at your end. It's not working at my end. May be I am missing something. I want to get rid of a value where there is 6 9s only, NOT 99999999.

DATA Have;
INPUT ID VAR1$ VAR$ VAR6$;
DATALINES;
101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 999999 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX
;
RUN;

data want;
set have;
if strip(var4)="999999" then var4="";
run;

Thanks,

That doesn't work because you have changed the name of the variable. In your data step you have

INPUT ID VAR1$ VAR$ VAR6$;

so there is no VAR4.

Re: Removing one specific value

SuryaKiran — Wed, 07 Mar 2018 23:34:46 GMT

Yes, It works fine for me. Try COMPRESS() to remove any blanks between 9's

DATA WANT;
SET HAVE;
VAR4=IFC(COMPRESS(var4)="999999"," ",var4);
run;

Re: Removing one specific value

RW9 — Thu, 08 Mar 2018 09:29:31 GMT

No this code does not work, the reason is quite clear:

DATA Have;
INPUT ID VAR1$ VAR$ VAR6$;

^ ^ Variable is called var here, not var4 hence the use of var4 in next step fails.
DATALINES;
101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 999999 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX
;
RUN;
data want;
set have;
if strip(var4)="999999" then var4="";

^ ^ no var4 in dataset!!
run;

This works fine (note how I use the code window and apply good formatting!):

data have;
  input id var1 $ var4 $ var6 $;
datalines;
101 ENG1 15225555 NY
105 Che1 10222541 NY
109 Eng2 999999 CA
115 Phy2 1156858 PA
201 Che1 99999999 TX
;
run;;

data want;
  set have;
  if strip(var)="999999" then var="";
run;