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

I have a data set that looks something like this:

 

data have;
infile datalines delimiter=','; 
input IDnumber String $50.; 
datalines;           
2477,Cat Dog A blue car
2431,Cat Fish Yelllow Submarine 
;             
run;

I need to extract each data item from the string to one row so that i get this:

 

 

data want;
infile datalines delimiter=','; 
input IDnumber String $50.; 
datalines;           
2477,Cat
2477,Dog
2477,A blue car
2431,Cat
2431,Fish 
2431,Yelllow Submarine 
;             
run;

The text will sometimes be split by a single word, other times several words constitute one phrase, and one phrase can sometimes start with a capital letter followed by a small letter in the next word, but othertimes it has capital letters in both words of one phrase, so I cannot use regexp or the like to do a sort of conditional do loop or array. I basically need to hardcode it so something like this, but cant quite wrap my head around the right syntax:

 

 

data try;
set have;
do ... ;
if index(String,'Cat') > 0 then output Phrase = 'Cat' ;
if index(String,'Dog') > 0 then output Phrase = 'Dog' ;
if index(String,'A blue car') > 0 then output Phrase = 'A blue car' ;
if index(String,'Fish') > 0 then output Phrase = 'Fish' ;
if index(String,'Yellow submarine') > 0 then output Phrase = 'Yellow submarine' ;
;
end;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

An interesting issue. You don't need a loop, just and do...end and swap output-statement and assignment.

data try;
set have;

if index(String,'Cat') > 0 then do; Phrase = 'Cat' ; output; end;
...
run;

View solution in original post

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, I don't see logically how I would know that "A blue car" is a separate from another string.  I mean its simple enough to split by word:

data want;
  length word $200;
  set have;
  do i=1 to countw(string," ");
    word=scan(string,i," ");
    output;
  end;
run;

But I don't see anything logical about how to get Yellow Submarine or A blue car?

andreas_lds
Jade | Level 19

An interesting issue. You don't need a loop, just and do...end and swap output-statement and assignment.

data try;
set have;

if index(String,'Cat') > 0 then do; Phrase = 'Cat' ; output; end;
...
run;

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
  • 2 replies
  • 2146 views
  • 0 likes
  • 3 in conversation