DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

 

 

 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;

 

 


Accepted Solutions
Solution
‎10-05-2017 08:04 AM
Super User
Super User
Posts: 9,441

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

 

 

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

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,441

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

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=...);
Occasional Contributor
Posts: 7

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

Thanks a lot

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




Solution
‎10-05-2017 08:04 AM
Super User
Super User
Posts: 9,441

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

 

 

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

 

Valued Guide
Posts: 525

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 122 views
  • 0 likes
  • 3 in conversation