BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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,

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

11 REPLIES 11
kiranv_
Rhodochrosite | Level 12

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';
RW9
Diamond | Level 26 RW9
Diamond | Level 26
data want;
  set have;
  if index(var4,"999999") then delete;
run;
mlogan
Lapis Lazuli | Level 10

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,

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
mlogan
Lapis Lazuli | Level 10
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,
ballardw
Super User

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

Astounding
PROC Star

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.

SuryaKiran
Meteorite | Level 14
DATA WANT;
SET HAVE;
VAR4=IFC(strip(var4)="999999"," ",var4);
run;
Thanks,
Suryakiran
mlogan
Lapis Lazuli | Level 10
Hi SuryaKiran,
Is the code working for you?
SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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