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,
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;
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';
data want; set have; if index(var4,"999999") then delete; run;
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,
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 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.
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;
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.
DATA WANT;
SET HAVE;
VAR4=IFC(strip(var4)="999999"," ",var4);
run;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.