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

Hi,

 

suppsoe that i ave the following data:

description

A car

1 car 23j @

Car

cars

2 trucks

One truck

One truck and one car

What I would like to have is to create a new data set where in the description either "car" or "truck" is present, so the new data table is:

 

description

A car

1 car 23j @

Car

One truck

One truck and one car

 

Thnak you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

Yes there is way to put the list into the function directly rather than passing each word, for this you need to create a macro variable which carries these words. Also it depends on the function which you are going to use.

 

For example, as i mentioned in the my previous reply about the prxmatch which could be used for checking the words, this function requires the words to be checked separated by pipe '|' so when you create the macro variables you need to have the words separated by pipe.

 

please try something like below

 

proc sql;
select distinct words into : word separated by '|' from have;
quit;

data have;
set want;
if prxmatch('m/\b(&word)\b/i',description)>0;
run;

Thanks,
Jag

View solution in original post

10 REPLIES 10
PGStats
Opal | Level 21

Use IF FINDW(description, "car", " ", "i") > 0 OR FINDW(description, "truck", " ", "i") > 0;

PG
Jagadishkatam
Amethyst | Level 16

Alternatively you could use the perl regular expression like

prxmatch('m/(car|truck)\b/i',description)>0
Thanks,
Jag
PGStats
Opal | Level 21

@Jagadishkatam you would need a word boundary before the words too 'm/\b(car|truck)\b/i', otherwise you will match anything like "I was struck by lightning" 

PG
Jagadishkatam
Amethyst | Level 16
@PGStats thank you for the correction
Thanks,
Jag
ilikesas
Barite | Level 11

Hi,

 

thank you for replying!!!

 

Just another small extension of the question, if I have a list of words, say 10, is it possible to put the list instead of each word individually?

 

 

Thnka you!

pearsoninst
Pyrite | Level 9
/*
Yes, You can in this code (word,"car","truck","ANYTHINHYOUWANT); you specify which word you need to find
find 
dataiwant created a new dataset with the result;

*/

data have dataiwant; 
input word$ 30.;
matchingword = find(word,"car","truck");
if matchingword > 0 then output dataiwant;
drop  matchingword;
datalines; 
A car
1 car 23j @
Car
cars
2 trucks
One truck
One truck and one car
;
run;
Proc Print data = dataiwant;
run;
Tom
Super User Tom
Super User

That is not the right syntax for the FIND() function.

Jagadishkatam
Amethyst | Level 16

Yes there is way to put the list into the function directly rather than passing each word, for this you need to create a macro variable which carries these words. Also it depends on the function which you are going to use.

 

For example, as i mentioned in the my previous reply about the prxmatch which could be used for checking the words, this function requires the words to be checked separated by pipe '|' so when you create the macro variables you need to have the words separated by pipe.

 

please try something like below

 

proc sql;
select distinct words into : word separated by '|' from have;
quit;

data have;
set want;
if prxmatch('m/\b(&word)\b/i',description)>0;
run;

Thanks,
Jag
Tom
Super User Tom
Super User

Don't you need to use double quotes to have the macro variable reference resolve?

Jagadishkatam
Amethyst | Level 16
Thank you @Tom for the correction, It should be prxmatch("m/\b(&word)\b/i",description)>0;
replaced the single quotes with double quotes
Thanks,
Jag

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 1656 views
  • 7 likes
  • 5 in conversation