BookmarkSubscribeRSS Feed
RoWa
Calcite | Level 5

Hello,

 

I am trying to delete the observation that does NOT contain "Card", and I am doing it in the data step. it gave me error on the =* and delete. I am not too sure what was wrong with it, can you please help?

 

can I do this in the sql step??

 

Thank you. 

 

data want;

set have;

if acct_name not =* 'CARD' then delete;

run;

5 REPLIES 5
nehalsanghvi
Pyrite | Level 9
SQL version would be:

Proc sql;
Create table want as
Select *
From have
Where acct_name not like '%CARD%';
Quit;
nehalsanghvi
Pyrite | Level 9
Oops, please exclude the 'not' from the query I posted. I did the opposite above. This is what you need:

Proc sql;
Create table want as
Select *
From have
Where acct_name like '%CARD%';
Quit;
Reeza
Super User

Sounds like is a SQL operator, not a data step operator. 

 

But you could use it in a WHERE statement, use a SQL query instead or use the FIND or INDEX functions in place of it. One thing the INDEX and FIND can do is ignore case, if specified to do so.

 

 

art297
Opal | Level 21

You can use the contains operator in a datastep where clause. e.g.:

 

data want;
  set have (where=(acct_name contains 'CARD'));
run;

HTH,

Art, CEO, AnalystFinder.com

 

ChrisNZ
Tourmaline | Level 20

While IF and WHERE are largely interchangeable from a syntax view point, some operators or functions can only be used in one of them.

 

WHERE can use operators such as like, contains, sounds like, or between as they come from the SQL world where WHERE clauses are used.

 

IF can use functions such as lag or vvalue or attrn as these belong firmly to the SAS world only.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1684 views
  • 0 likes
  • 5 in conversation