topic Re: Clarification on deleting observations from dataset in SAS Procedures

Clarification on deleting observations from dataset

NKormanik — Thu, 21 Feb 2013 10:18:52 GMT

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

Re: Clarification on deleting observations from dataset

damanaulakh88 — Thu, 21 Feb 2013 10:34:26 GMT

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

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

Re: Clarification on deleting observations from dataset

damanaulakh88 — Thu, 21 Feb 2013 10:44:40 GMT

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

Re: Clarification on deleting observations from dataset

UrvishShah — Thu, 21 Feb 2013 11:16:41 GMT

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

Re: Clarification on deleting observations from dataset

NKormanik — Fri, 22 Feb 2013 02:07:47 GMT

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!

Re: Clarification on deleting observations from dataset

damanaulakh88 — Fri, 22 Feb 2013 03:48:58 GMT

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

Re: Clarification on deleting observations from dataset

NKormanik — Fri, 22 Feb 2013 07:25:27 GMT

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

Re: Clarification on deleting observations from dataset

damanaulakh88 — Fri, 22 Feb 2013 08:29:11 GMT

Hi ,

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

For Example:-

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

or Combo like '%23601%' - CORRECT

or Combo like "%23601%"- WRONG

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

/Daman

Re: Clarification on deleting observations from dataset

NKormanik — Fri, 22 Feb 2013 08:31:26 GMT

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;

Re: Clarification on deleting observations from dataset

NKormanik — Fri, 22 Feb 2013 09:23:00 GMT

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;