topic Re: How to compress data value in SAS Programming

How to compress data value

abhee — Tue, 09 Dec 2014 07:53:25 GMT

I need to compress one of the value in the character string.

e..g, if value of string is "mango,orange,papaya" and I want to create a new variable with value "orange,papaya". I used below code:

new_var = compress(string,"mango");

and the result was new_var = ,re,ppy

Please suggest how can I do that?

Re: How to compress data value

LinusH — Tue, 09 Dec 2014 08:04:14 GMT

Use findw and substr functions.

Re: How to compress data value

abhee — Tue, 09 Dec 2014 08:06:11 GMT

Could you please suggest sample syntax?

Thanks,

Abhee

Re: How to compress data value

LinusH — Tue, 09 Dec 2014 08:07:31 GMT

Support.sas.com

Re: How to compress data value

Patrick — Tue, 09 Dec 2014 08:08:18 GMT

Compress() will remove from the source string ALL characters listed in the compress function.

If this is not just a study question then you will have to be a bit more specific and provide some sample data with expected results for us to come up with an appropriate solution.

Below code will replace the string "papaya" with a blank.

data test;

have="mango,orange,papaya";

want = tranwrd(have,"papaya","");

run;

Re: How to compress data value

abhee — Tue, 09 Dec 2014 08:16:41 GMT

Thanks for your prompt response. However I want to remove mango. I used below code:

data test;

have="mango,orange,papaya";

want = tranwrd(have,"mango","");

run;

and the result was: want = ,,orange,papaya

Re: How to compress data value

Patrick — Tue, 09 Dec 2014 08:33:48 GMT

So it worked then.

Re: How to compress data value

abhee — Tue, 09 Dec 2014 08:51:37 GMT

No, it did not. I am getting comma also as I have mentioned earlier.

e.g., ,,orange,papaya

I want: orange,papaya

Since I am working on confidential data, I am sorry I will not be able to share the sample data.

Re: How to compress data value

RW9 — Tue, 09 Dec 2014 09:32:35 GMT

Hi,

Try a do while loop over the data split by commas and then append each one where not your value to a new variable:

data have;
val="mango,orange,papaya";
output;
run;

data want (drop=i);
set have;
length processed $2000.;
i=1;
do while (scan(val,i,",") ne "");
if scan(val,i,",") ne "mango" then processed=catx(',',processed,scan(val,i,","));
i=i+1;
end;
run;

Re: How to compress data value

gergely_batho — Tue, 09 Dec 2014 09:33:16 GMT

Just add a comma (,) to the end of mango.

data test;

have="mango,orange,papaya";

want = tranwrd(have,"mango,","");

putlog want=;

run;

But if you want to handle situations, where mango can be at the beginning, end, and in the middle of the string... Maybe you need to use tranwrd 2 times. Once with "mango," then with ",mango".

Re: How to compress data value

abhee — Tue, 09 Dec 2014 09:54:36 GMT

Thanks RW9. It worked.

Re: How to compress data value

RW9 — Tue, 09 Dec 2014 09:55:45 GMT

The only thing I would add there Gergely is that you might end up with lots of tranwrds() nestled if you have more than one thing to remove, also you could run into problems trying to remove orange from "blood orange" for instance. You could of course expand it with arrays and loops:

data have;
val="mango,orange,papaya,fruit,veg,apple,kiwi";
output;
run;

data want (drop=i j x removes1-removes4);
set have;
array removes{4} $20. ("mango","fruit","veg","orange");
length processed $2000.;
i=1;
x=0;
do while (scan(val,i,",") ne "");
    do j=1 to 4;
      if scan(val,i,",")=removes{j} then x=1;
    end;
    if x=0 then processed=catx(',',processed,scan(val,i,","));
    i=i+1;
    x=0;
end;
run;

Re: How to compress data value

gergely_batho — Tue, 09 Dec 2014 10:56:03 GMT

I would use regexp, if I have to remove more then one thing

data have;

val="papaya,mango,mango,orange,papaya,fruit,veg,apple,kiwi,orange,orange,papaya,papaya";

output;

run;

data want (keep=processed);

array removes{4} $20. ("mango","fruit","veg","orange");

length processed $2000;

length tmp $200 ;

/*constructing the regexp*/

if _n_=1 then do;

do i=1 to dim(removes);

tmp=catx('|',tmp,'(,?'||strip(removes)||')');

end;

putlog tmp=;

regexp=cats('s/',tmp,'//');

putlog regexp=;

regexpid=prxparse(regexp);

end;

set have;

processed=prxchange(regexpid,-1,val);

processed=prxchange('s/(^,)//',-1,processed);/*removing ',' from beginning*/

putlog processed=;

run;

Re: How to compress data value

Vix — Tue, 09 Dec 2014 14:31:52 GMT

data have;
val="mango,orange,papaya";
output;
run;

data new;
set have;
val1= cats("'",substr(val,(find(val, ',')+1)),"'");
run;