topic Re: compress dataset PROC SQL in SAS Procedures

compress dataset PROC SQL

Planck — Wed, 04 Jan 2017 15:25:21 GMT

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

Re: compress dataset PROC SQL

ballardw — Wed, 04 Jan 2017 15:46:31 GMT

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

Re: compress dataset PROC SQL

Shmuel — Wed, 04 Jan 2017 15:50:33 GMT

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;

Re: compress dataset PROC SQL

Planck — Wed, 04 Jan 2017 16:15:32 GMT

Exactly what I needeed actually.

Thank you!