compress dataset PROC SQL

[Planck]

Hi,

I have a table.

I try to remove all possible accents from this table by doing something like:

 PROC SQL;
CREATE TABLE WORK.B AS
compress(
WORK.A,
"éèêëâäàåáïîìíçôöòóûùúü,.-'"
,"eeeeaaaaaiiiicoooouuuu ");
QUIT;
RUN;

But it's not working. It want a select and I don't know how to do it....

Have you an idea?

Thanks

[ballardw]

 PROC SQL;
CREATE TABLE WORK.B AS

   select *, compress(VariableA,"éèêëâäàåáïîìíçôöòóûùúü,.-'","eeeeaaaaaiiiicoooouuuu ") AS NewVariableA

From Work.A;
QUIT;

Would fix one variable. SQL does not have a mechanism for 1) default selection of variables you have to include a Select statement, 2) manipulating a variable you need to NAME the result using As varaible, 3) you have to specify a data source with FROM

If you have a number of variables whose names shouldn't change you would be better off looking at a data step to allow array processing.

Something along these lines:

data work.b;
   set work.a;
   array c  _character_;
   do i = 1 to dim(c);
      c[i] = compress(c[i],"éèêëâäàåáïîìíçôöòóûùúü,.-'","eeeeaaaaaiiiicoooouuuu ") ;
   end;
   drop i;
run;

which will process all of the character variables in work.a

[Shmuel · Accepted solution]

It seems to me that you meant using TRANSLATE instead COMPRESS and

I'm not sure you that can do it by SQL as you tried to do.

try next code:

data b;

 set a;

      array _chr   _character_ ;

       do over _chr;

            _chr = translate(_chr , "eeeeaaaaaiiiicoooouuuu " ,  "éèêëâäàåáïîìíçôöòóûùúü,.-'" );

       end;

run;

[Planck]

Exactly what I needeed actually.

Thank you!