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

Hi SAS community pros!

I am trying to create a data step and/or macro that will parse a large amount of text into individual words for further analysis.

I would like to give SAS a data set with text strings in one variable, and have it return one new variable with all the words from the input variable as separate observations and removing common (or specified list of) delimiters.

I have been experimenting with the SCAN function, but have not had training in how to execute loops or macro array variables.

For example, please see the below excel screenshot for a simplistic example.  Would like to start with SAS data set column A only, and return SAS data set columns A and B

Thank you for your assistance!

parse.png

1 ACCEPTED SOLUTION

Accepted Solutions
CTorres
Quartz | Level 8

Based on Reeza solution, how about:

data have;

a="Wolf, meeting with a Lamb astray from the fold,"; n+1; output;

a="resolved not to lay violent hands on him"; n+1; output;

a="but to find some plea"; n+1; output;

run;

data temp(keep=b j);

    set have;

    i=1;

    do while (scan(a, i) ne "");

        b=scan(a,i);

       j+1;

        output;

        i+1;

    end;

run;

proc sql noprint;

  create table want (drop=j) as

  select a,b,j

  from have right join temp

  on n=j

  order by j;

quit;

CTorres

View solution in original post

5 REPLIES 5
ballardw
Super User

This may get you started. This will duplicate the string. COUNTW and SCAN have a large number of possible combinations of delimiters.

data want;

   string = 'For example, please see the below excel screenshot for a simplistic example.';

   words = countw(string,'sp');

   do i=1 to words;

      word = scan(string,i,'sp');

      output;

   end;

   drop i words;

run;

Reeza
Super User

Not quite what you asked for, but I'm not sure having the A column as you requested would be useful either.

data have;

a="Wolf, meeting with a Lamb astray from the fold,";output;

a="resolved not to lay violent hands on him"; output;

a="but to find some plea"; output;

run;

data want;

    set have;

    i=1;

    do while (scan(a, i) ne "");

        b=scan(a,i);

        output;

        i+1;

    end;

run;

CTorres
Quartz | Level 8

Based on Reeza solution, how about:

data have;

a="Wolf, meeting with a Lamb astray from the fold,"; n+1; output;

a="resolved not to lay violent hands on him"; n+1; output;

a="but to find some plea"; n+1; output;

run;

data temp(keep=b j);

    set have;

    i=1;

    do while (scan(a, i) ne "");

        b=scan(a,i);

       j+1;

        output;

        i+1;

    end;

run;

proc sql noprint;

  create table want (drop=j) as

  select a,b,j

  from have right join temp

  on n=j

  order by j;

quit;

CTorres

crosskonaftw
Fluorite | Level 6

Thank you all for your input!  Apologies for the delayed response as I have been sick and travelling.

I am going to use all of your suggestions as a working starting template and improve upon it.  Thanks again!

kjohnsonm
Lapis Lazuli | Level 10

Great question, I needed this exact answer today!

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
  • 5941 views
  • 14 likes
  • 5 in conversation