Clarification on deleting observations from dataset

[NKormanik]

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

[damanaulakh88]

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

=============

[damanaulakh88 · Accepted solution]

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

[NKormanik]

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;

[NKormanik]

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!

[damanaulakh88]

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

[NKormanik]

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

[damanaulakh88]

Hi ,

Run it with Single Quotes , it will work then:-

For Example:-

=====================

or Combo like '%23601%' - CORRECT

or Combo like "%23601%"- WRONG

=====================

/Daman

[NKormanik]

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;

[UrvishShah]

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