topic Re: translating a Microsoft Access SQL query to A SAS Proc SQL query in SAS Programming

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

Stefy67 — Thu, 05 Oct 2017 09:27:02 GMT

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;

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

RW9 — Thu, 05 Oct 2017 10:31:40 GMT

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=...);

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

Stefy67 — Thu, 05 Oct 2017 10:40:35 GMT

Thanks a lot

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

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

andreas_lds — Thu, 05 Oct 2017 11:04:24 GMT

"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.

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

RW9 — Thu, 05 Oct 2017 11:13:57 GMT

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=p1pca7vwjjwucin178l8qddjn0gi.htm&locale=en