I would like to delete an observation where under the variable "Combo" the word "Low" is found, in any combination with other words.
For instance:
variable: Combo
observation: Low_A
observation: B_Low
etc.
The above observations should be removed, because the word "Low" is there, under the "Combo" variable.
I thought the code below would do the trick, but it doesn't:
data nicholas._21603_;
modify nicholas._21603_;
if find(Combo,"Low") then remove;
run;
Please advise.
Thanks,
Nicholas Kormanik
And moreover if you want to do it through dataset then below is the code:-
==============
data final;
set new;
if index(COMBO,'Low') >0 then delete;
run;
==============
/Daman
Hi Nicholas,
Try this code this is working:-
============
data new;
input sno combo $;
datalines;
1 Low_A
2 Low_B
3 High_c
;
run;
proc sql;
create table final as select * from new except select * from new where combo like '%Low%';
quit;
=====================
Output:-
============
Obs sno combo
1 3 High_c
=============
And moreover if you want to do it through dataset then below is the code:-
==============
data final;
set new;
if index(COMBO,'Low') >0 then delete;
run;
==============
/Daman
damanaulakh88, the second way you provided to do what was requested worked flawlessly. Thank you so very much!
data nicholas._combined_trimmed ;
set nicholas._combined_ ;
if index(combo,"23601" ) >0 then delete;
if index(combo,"23602" ) >0 then delete;
if index(combo,"23603" ) >0 then delete;
if index(combo,"23604" ) >0 then delete;
if index(combo,"23605" ) >0 then delete;
if index(combo,"23606" ) >0 then delete;
if index(combo,"AvgPrc21" ) >0 then delete;
if index(combo,"AvgVol21" ) >0 then delete;
if index(combo,"Close" ) >0 then delete;
if index(combo,"DayofWeek") >0 then delete;
if index(combo,"High" ) >0 then delete;
if index(combo,"Low" ) >0 then delete;
if index(combo,"Open" ) >0 then delete;
if index(combo,"PxV21" ) >0 then delete;
if index(combo,"Volume" ) >0 then delete;
run;
Excellent job, damanaulakh88.
Here is the code I used:
proc sql;
create table nicholas._21603_trimmed
as select *
from nicholas._21603_
except select * from nicholas._21603_
where Combo like '%Low%';
quit;
Follow-up question... If there is a second word I'd like to look for as well, can I
a) combine that into the present code you provided?, or
b) must I issue a second complete command set for each such additional word?
For instance:
-- Also look for the word "High", and if Combo has any observations with that word, remove those observation as well.
Please amend the above code, if possible.
Thanks!
Hi Nicholas,
Here you go with the modified code :-
proc sql;
create table nicholas._21603_trimmed
as select *
from nicholas._21603_
except select * from nicholas._21603_
where Combo like '%Low%' or combo like'%High%';
quit;
/Daman
damanaulakh88, here is the log of the full code I'm now trying to use. Unfortunately I am getting some errors. Wonder if you can spot what might be wrong? Using SAS 9.2, by the way. Thanks!
proc sql;
create table nicholas._Combined_trimmed
as select *
from nicholas._Combined_
except select * from nicholas._Combined_
where Combo like "%xxxxxxx%"
WARNING: Apparent invocation of macro XXXXXXX not resolved.
or Combo like "%23601%"
or Combo like "%23602%"
or Combo like "%23603%"
or Combo like "%23604%"
or Combo like "%23605%"
or Combo like "%23606%"
or Combo like "%AvgPrc21%"
WARNING: Apparent invocation of macro AVGPRC21 not resolved.
or Combo like "%AvgVol21%"
WARNING: Apparent invocation of macro AVGVOL21 not resolved.
or Combo like "%Close%"
ERROR: Macro keyword CLOSE is not yet implemented.
or Combo like "%DayofWeek%"
WARNING: Apparent invocation of macro DAYOFWEEK not resolved.
or Combo like "%High%"
WARNING: Apparent invocation of macro HIGH not resolved.
or Combo like "%Low%"
WARNING: Apparent invocation of macro LOW not resolved.
or Combo like "%Open%"
ERROR: Macro keyword OPEN is not yet implemented.
or Combo like "%PxV21%"
WARNING: Apparent invocation of macro PXV21 not resolved.
or Combo like "%Volume%"
WARNING: Apparent invocation of macro VOLUME not resolved.
;
NOTE: Table NICHOLAS._COMBINED_TRIMMED created, with 6112701 rows and 15 columns.
quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 2:54.05
cpu time 45.08 seconds
Hi ,
Run it with Single Quotes , it will work then:-
For Example:-
=====================
or Combo like '%23601%' - CORRECT
or Combo like "%23601%"- WRONG
=====================
/Daman
Completely correct. Both approaches worked perfectly. The one marked correct was the faster of the two, considerably faster.
Thanks again,damanaulakh88!
proc sql;
create table nicholas._Combined_trimmed
as select *
from nicholas._Combined_
except select * from nicholas._Combined_
where Combo like '%xxxxxxx%'
or Combo like '%23601%'
or Combo like '%23602%'
or Combo like '%23603%'
or Combo like '%23604%'
or Combo like '%23605%'
or Combo like '%23606%'
or Combo like '%AvgPrc21%'
or Combo like '%AvgVol21%'
or Combo like '%Close%'
or Combo like '%DayofWeek%'
or Combo like '%High%'
or Combo like '%Low%'
or Combo like '%Open%'
or Combo like '%PxV21%'
or Combo like '%Volume%'
;
quit;
Hi,
MODIFY statement whould not replace your data that if you supply the same named in your Data or MODIFY Statement and i think REMOVE statement affects the observation to be written to data set though you use the same name in DATA or MODIFY statement...So it must be work...
I think there is some access prohibition for your dataset(read-only) or library(read-only)...
data test;
set sashelp.class;
run;
data test;
modify test;
if find(sex,"M") then remove;
run;
I tried the above SAS Code and it works...
Thanks,
Urvish
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.