topic Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4? in SAS Programming

How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

inquistive — Sun, 04 Dec 2022 22:45:58 GMT

data employees;
length emp_id $2. name $5.  emp_type $4.  emp_stat $8.;
   input emp_id name  emp_type emp_stat ;
datalines;
01 Sandy sea contrct
02 Dandy fte reg
03 Candy sea pttime
04 Mandy fte reg
;

data category;
length emp_cat1 $12  
       emp_cat2 $12;
       retain emp_id
              name;
set employees;
 emp_cat1= catx(",", emp_type, emp_stat);
 emp_cat2= catx(" ", emp_type, emp_stat);
 drop emp_type emp_stat;
 run;
 
 data emp_tmp;
 length emp_stat1 $12  
        emp_stat2 $12;
 retain emp_id
        name;
 set category;
 emp_stat1= compbl(tranwrd(emp_cat1, ",", ""));
 emp_stat2= compbl(tranwrd(emp_cat2, "", ","));
 run;
  
 data emp;
 length comma $12  
        blank $12;
        retain emp_id
               name
               ;
 set emp_tmp;
 comma= compbl(tranwrd(emp_stat1,"","," ));
 blank= compbl(tranwrd(emp_stat2, ",",""));
 drop emp_stat1 emp_stat2;
run;

The last step in the above code creates following table EMP:

The addition of blank (' ') between strings has returned a desired output. But the addition of comma(,) between strings has returned so many undesired commas. Main question/concern is how to remove the UNNECESSARY commas (especially at the end)in the data/records returned under the COMMA column? I can reduce the length of variable to 11 char to get rid of a comma (in the first row: sea, contrct,) but that won't solve the issues with the other rows that have shorter strings. There will still be unnecessary commas.

Any help would be greatly appreciated.

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

PaigeMiller — Sun, 04 Dec 2022 22:44:53 GMT

Seems to me that when you create data set CATEGORY that you get what you want

so I don't understand why additional processing is necessary here, it seems to me that the solution is to stop the program at this point.

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

inquistive — Sun, 04 Dec 2022 22:48:14 GMT

Yes, that's true! But there are situations where we read data from various sources and they are not quite clean or formatted like this. The above is for example only to make the issue clearer.

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

Tom — Sun, 04 Dec 2022 23:05:40 GMT

So this statements is confusing because of the way you left out the space inside of the string literal.

comma= compbl(tranwrd(emp_stat1,"",","));

You are running this code:

comma= compbl(tranwrd(emp_stat1," ",","));

So the TRANWRD() function will converting all of the spaces into commas (including any trailing spaces used to fill up the fixed length string EPM_STAT1).

So a value like "A B C " would become "A,B,,,C,,,,"

Then the COMPBL() function as nothing to operate on since there cannot be any spaces left.

The other statement makes more sense:

blank= compbl(tranwrd(emp_stat2,","," "));

So the TRANWRD() converts all of the commas into spaces. Then the COMPBL() collapses multiple spaces into one.

So a value like "A,B,,,C,,,," would become "A B C"

I am not exactly sure what your goal is. Do you want to convert a value like "a,,,b,,,,c" into "a,b,c" ?

If so then use something like:

comma= translate(compbl(translate(strip(emp_stat1),' ,',', ')),' ,',', ');

So remove the leading and trailing spaces. Swap the commas and spaces, compress the multiple spaces (which were commas), and the swap the commas and spaces again.

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

PaigeMiller — Sun, 04 Dec 2022 23:23:17 GMT

@inquistive wrote:

Yes, that's true! But there are situations where we read data from various sources and they are not quite clean or formatted like this. The above is for example only to make the issue clearer.

I'm not following. You can fix poorly formatted data, and then your placement of commas is simple.

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

inquistive — Mon, 05 Dec 2022 00:15:09 GMT

Thank you very much @Tom . You provided me with what I was looking for.

Yes, I messed up (unintentionally😔) with the extra blank space:

comma= compbl(tranwrd(emp_stat1," ",","));

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

Tom — Mon, 05 Dec 2022 00:17:19 GMT

@inquistive wrote:

Thank you very much @Tom . You provided me with what I was looking for.

Yes, I messed up (unintentionally😔) with the extra blank space:
comma= compbl(tranwrd(emp_stat1," ",","));

That looks wrong. That is what you had before.

Did you mean to call the functions in the opposite order? So compress multiple blanks to one and then convert them to commas?

Re: How can I add/remove comma(s) or blank(s) between words in a column value in SAS 9.4?

inquistive — Mon, 05 Dec 2022 00:21:09 GMT

@Tom,

Yes, I copied and pasted the code where I made a mistake (that you pointed out in your response). I was referring to the place where the problem lies in my code.

The code you provided is the perfect solution😄.

Thanks,