topic Re: How to convert comma delimited values to multiple rows when comma is present in values in SAS Programming

How to convert comma delimited values to multiple rows when comma is present in values

Siva_Harish — Wed, 24 May 2023 15:08:36 GMT

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

Re: How to convert comma delimited values to multiple rows when comma is present in values

Seadrago — Wed, 24 May 2023 15:22:13 GMT

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"

Re: How to convert comma delimited values to multiple rows when comma is present in values

Tom — Wed, 24 May 2023 15:47:11 GMT

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.

Re: How to convert comma delimited values to multiple rows when comma is present in values

Reeza — Wed, 24 May 2023 15:54:44 GMT

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;