BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NKormanik
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
damanaulakh88
Obsidian | Level 7

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

9 REPLIES 9
damanaulakh88
Obsidian | Level 7

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
Obsidian | Level 7

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
Barite | Level 11

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
Barite | Level 11

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
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

Hi ,

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

For Example:-

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

or Combo like '%23601%' - CORRECT

or Combo like "%23601%"- WRONG

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

/Daman

NKormanik
Barite | Level 11

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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