translating a Microsoft Access SQL query to A SAS Proc SQL query

[Stefy67]

 I want to use TRANSLATE  in more than one lines in the program (see example1) but the instruction doesn’t work. I works only if I write one line at a time. Is it correct? 

Do I have to split each time for different dataset  and write one instruction at a time such Example 2? Is there a way to write all the instruction together? 

Thank you

Stefy67

Example1:

data tre;

set due;

nomenew3 = translate(nomenew2,'AAAAAAAAAAA','ÀÁÂÃÄÅÆĀĂĄǍ');

cognomenew3 = translate(cognomenew2,'AAAAAAAAAAA','ÀÁÂÃÄÅÆĀĂĄǍ');

nomenew3 = translate(nomenew2, 'IIIIIIIIII' , 'ÌÍÎÏĨĪĬĮİǏ');
nomenew3 = translate(nomenew2, 'OOOOOOOOO' , 'ÒÓÔÕÖŌŎŐǑ');

cognomenew3 = translate(cognomenew2, 'IIIIIIIIII' , 'ÌÍÎÏĨĪĬĮİǏ');
cognomenew3 = translate(cognomenew2, 'OOOOOOOOO' , 'ÒÓÔÕÖŌŎŐǑ');

run;

Example2:

data tre;

set due;

nomenew3 = translate(nomenew2,'AAAAAAAAAAA','ÀÁÂÃÄÅÆĀĂĄǍ'); run; 

data tre1;

set tre;

cognomenew3 = translate(cognomenew2,'AAAAAAAAAAA','ÀÁÂÃÄÅÆĀĂĄǍ'); run;

[RW9]

I would question why you have to do this multiple times.  Anyways how about a small macro to do it (not tested):

%macro TranL (inds=,outds=,lvar=);

  data &outds.;
    set &inds.;
    array varlist &lvar.;
    do over varlist;
      varlist=translate(varlist,'AAAAAAAAAAA','ÀÁÂÃÄÅÆĀĂĄǍ');
      varlist=translate(varlist,'IIIIIIIIII' , 'ÌÍÎÏĨĪĬĮİǏ');
      ...
    end;
  run;

%mend TranL;

%TranL (inds=tre,outds=due,lvar=nomenew3 cognomenew3);
%TranL (inds=tre,outds=tre1,lvar=...);

[Stefy67]

Thanks a lot

But I want more varlist=translate ....... in one dataset 'outs' and not many 'outds' (ex. Due, tre1, ...)

[RW9 · Accepted solution]

Well, the function basechar should get you a fairways to what you want:

data dec;
  length code $200;
  input code $;
  want_string=basechar(code);
datalines;
AÀÁZÂÃÄÅÆĀĂĄǍ
ÌIÍÎIÏĨĪĬĮİǏ
;
run;

It will not get all of them, depending on your encoding.  

The other way is to step through each character at a time, using the char(<string>,<pos>) function, and split out the multi-byte characters and replace that way.  This shows what each of the characters byte numbers are - you will note on the special characters they have two, a table number and code within that table.  If you know the sequence you can then search for the various table numbers and the series of characters and replace with a given base table number.

data dec;
  length code $200;
  input code $;
  array cs{30};
  do i=1 to lengthn(code);
    cs{i}=rank(char(code,i));
  end;
datalines;
AÀÁZÂÃÄÅÆĀĂĄǍ
ÌIÍÎIÏĨĪĬĮİǏ
;
run;

 This page might be useful to you:

http://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=nlsref&docsetTarget=p1pca7vwjjw...

[andreas_lds]

"Does not work" can be the result of anything. So, please post input data as datastep, and full log output and describe what you expected to happen.