DATA Step, Macro, Functions and more

Removing one specific value

Accepted Solution Solved
Reply
Regular Contributor
Posts: 243
Accepted Solution

Removing one specific value

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,


Accepted Solutions
Solution
‎03-08-2018 08:22 PM
Super User
Super User
Posts: 9,465

Re: Removing one specific value

[ Edited ]

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;

 

View solution in original post


All Replies
PROC Star
Posts: 504

Re: Removing one specific value

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';
Super User
Super User
Posts: 9,465

Re: Removing one specific value

data want;
  set have;
  if index(var4,"999999") then delete;
run;
Regular Contributor
Posts: 243

Re: Removing one specific value

[ Edited ]

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,

Super User
Super User
Posts: 9,465

Re: Removing one specific value

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;
Regular Contributor
Posts: 243

Re: Removing one specific value

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,
Super User
Posts: 13,365

Re: Removing one specific value


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.

Solution
‎03-08-2018 08:22 PM
Super User
Super User
Posts: 9,465

Re: Removing one specific value

[ Edited ]

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;

 

Super User
Posts: 6,648

Re: Removing one specific value

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.

Valued Guide
Posts: 563

Re: Removing one specific value

DATA WANT;
SET HAVE;
VAR4=IFC(strip(var4)="999999"," ",var4);
run;
Thanks,
Suryakiran
Regular Contributor
Posts: 243

Re: Removing one specific value

Posted in reply to SuryaKiran
Hi SuryaKiran,
Is the code working for you?
Valued Guide
Posts: 563

Re: Removing one specific value

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;

 

 

Capture.PNG

Thanks,
Suryakiran
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 153 views
  • 6 likes
  • 6 in conversation