Help using Base SAS procedures

Clarification on deleting observations from dataset

Accepted Solution Solved
Reply
Regular Contributor
Posts: 225
Accepted Solution

Clarification on deleting observations from dataset

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


Accepted Solutions
Solution
‎02-21-2013 05:44 AM
Frequent Contributor
Posts: 81

Re: Clarification on deleting observations from dataset

Posted in reply to damanaulakh88

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

View solution in original post


All Replies
Frequent Contributor
Posts: 81

Re: Clarification on deleting observations from dataset

Posted in reply to NicholasKormanik

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

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

Solution
‎02-21-2013 05:44 AM
Frequent Contributor
Posts: 81

Re: Clarification on deleting observations from dataset

Posted in reply to damanaulakh88

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

Regular Contributor
Posts: 225

Re: Clarification on deleting observations from dataset

Posted in reply to damanaulakh88

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;



Regular Contributor
Posts: 225

Re: Clarification on deleting observations from dataset

Posted in reply to damanaulakh88

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!

Frequent Contributor
Posts: 81

Re: Clarification on deleting observations from dataset

Posted in reply to NicholasKormanik

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

Regular Contributor
Posts: 225

Re: Clarification on deleting observations from dataset

Posted in reply to damanaulakh88

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

Frequent Contributor
Posts: 81

Re: Clarification on deleting observations from dataset

Posted in reply to NicholasKormanik

Hi ,

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

For Example:-

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

or Combo like '%23601%' - CORRECT

or Combo like "%23601%"- WRONG

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

/Daman

Regular Contributor
Posts: 225

Re: Clarification on deleting observations from dataset

Posted in reply to damanaulakh88

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;

Regular Contributor
Posts: 195

Re: Clarification on deleting observations from dataset

Posted in reply to NicholasKormanik

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 390 views
  • 8 likes
  • 3 in conversation