BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Siva_Harish
Obsidian | Level 7

Hello,

 

Can anyone help to achieve below :

 

I am getting input values as comma separated values and again comma in the values as nested comma. I want to make multiple rows based on comma separated and not on based nested comma values.  

 

Input Required
Business Meeting - Involved, MTNG - Meals > 5,000XYZ, XYZ & ABC > 5,000XYZ, XYZ - Meal > 5,000XYZ Business Meeting - Involved
MTNG - Meals > 5,000XYZ
XYZ & ABC > 5,000XYZ
XYZ - Meal > 5,000XYZ
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Remove the comma's from the numbers is the easiest solution.

 

/* Sample input data */
data have;
    input text $100.;
    datalines;
Business Meeting - Involved, MTNG - Meals > 5,000XYZ, XYZ & ABC > 5,000XYZ, XYZ - Meal > 5,000XYZ
;

data want;
    set have;
    *remove commas in numbers;
    new_text=prxchange('s/(\d+),(\d+)/$1$2/', -1, text);
    *count number of terms;
    nterms=countc(new_text, ",");
    *expand out;

    do i=1 to nterms+1;
        term=scan(new_text, i, ",");
        output;
    end;
run;

/* View the output */
proc print data=want;
run;

View solution in original post

3 REPLIES 3
Seadrago
Obsidian | Level 7
I'm not sure if there is any easy way to do this. The options I can think of are either change your comma seperator/deliminator to another character eg: ";" OR remove the comma from "5,000"
Tom
Super User Tom
Super User

The surest way is to fix the process that generated the values so that it makes something that can be scanned.

 

  • Either use a delimiter that cannot appear in the data.
  • Or add quotes around the values (or at least the values that contain the delimiters).

Then you can use the Q option on the SCAN() function to parse the string.

 

Example of the second option:

data have;
  string='Business Meeting - Involved,"MTNG - Meals > 5,000XYZ","XYZ & ABC > 5,000XYZ","XYZ - Meal > 5,000XYZ"';
run;

data want;
  set have;
  do index=1 to countw(string,',','q');
    length value $40 ;
    value = dequote(scan(string,index,',','q'));
    output;
  end;
run;
Obs    index               value

 1       1      Business Meeting - Involved
 2       2      MTNG - Meals > 5,000XYZ
 3       3      XYZ & ABC > 5,000XYZ
 4       4      XYZ - Meal > 5,000XYZ

If looks like you are actually using comma and space as the delimiter. So perhaps you can split on those.

data have;
  string='Business Meeting - Involved, MTNG - Meals > 5,000XYZ, XYZ & ABC > 5,000XYZ, XYZ - Meal > 5,000XYZ';
run;

data want;
  set have;
  loc=1;
  do index=1 by 1 until(nextloc=0);
    nextloc=find(string,', ',loc);
    length value $40 ;
    if nextloc then value=substrn(string,loc,nextloc-loc-1);
    else value=substrn(string,loc);
    output;
    loc=nextloc+2;
  end;
run;

proc print;
run;

Otherwise you might try to remove (or replace) the commas that appear between digits.  Perhaps use regular expressions. And then you can scan using comma as the delimiter.

 

Reeza
Super User

Remove the comma's from the numbers is the easiest solution.

 

/* Sample input data */
data have;
    input text $100.;
    datalines;
Business Meeting - Involved, MTNG - Meals > 5,000XYZ, XYZ & ABC > 5,000XYZ, XYZ - Meal > 5,000XYZ
;

data want;
    set have;
    *remove commas in numbers;
    new_text=prxchange('s/(\d+),(\d+)/$1$2/', -1, text);
    *count number of terms;
    nterms=countc(new_text, ",");
    *expand out;

    do i=1 to nterms+1;
        term=scan(new_text, i, ",");
        output;
    end;
run;

/* View the output */
proc print data=want;
run;

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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
  • 3 replies
  • 520 views
  • 1 like
  • 4 in conversation