Da Access SQL a SaS

[Tecla1]

Good morning,

I have to translate a series of Access queries in SaS, in particular I don't know how to translate the queries that call other queries... is there a translator available?

Many tnks for your kindly help.

Tecla 

[Kurt_Bremser]

In my experience, the best translator sits on the chair in front of the keyboard, and the more complex code is, the more true this gets.

With porting code to SAS, in-depth SAS knowledge is necessary, as you will often solve issues better with non-SQL tools.

[Tecla1]

Hi, many tnks for your kindly help, I try with ChatGPT and I receive a Good Replay. The problem are the query who recall some other query, so in the same time Access run al querys in the same time as : 

Where q_ultimi_ia_3 is another query. In a SQL can I recall another SQL?

"...................

INSERT INTO IA_base ( iau_id, azie_id, uop_cod, iab_amb, iau_dui, iab_vui, iab_pui, iab_ggt )
SELECT interventi_audit.iau_id, interventi_audit.azie_id, interventi_audit.uop_cod, 3 AS Espr2, interventi_audit.iau_dui, interventi_audit.iau_vro, giudizi_audit.gia_per, ExcelDays365(CDate([data]),[iau_dui]) AS gg
FROM (interventi_audit LEFT JOIN giudizi_audit ON interventi_audit.iau_vro = giudizi_audit.[gia_val]) INNER JOIN q_ultimi_ia_3 ON (interventi_audit.azie_id = q_ultimi_ia_3.azie_id) AND (interventi_audit.uop_cod = q_ultimi_ia_3.uop_cod) AND (interventi_audit.iau_dui = q_ultimi_ia_3.iau_dui) AND (interventi_audit.dataa = q_ultimi_ia_3.dataa)
ORDER BY interventi_audit.iau_dui DESC;

.................."

Tnks a lot !.

Tecla

[Tom · Accepted solution]

No idea what you mean by "RECALL".

If you want to replace a table reference like q_ultimi_ia_3 with a sub-query then just do that.

So if you had 

... INNER JOIN q_ultimi_ia_3 ON ...

You could run something like

... INNER JOIN (select a,c,c from x.y) q_ultimi_ia_3 ON ...

[Tecla1]

Tnks, 

"q_ultimi_ia_3 " is not a table , is another query. 

In Access is possible to use query like a "Matriosca" in SaS can I do that?

Tnks a lot.

Tecla 

[LinusH]

Not exactly, with that syntax.
One way is to define q_ultimi_ia_3 as view instead.
Or if this is the only place it's being used, paste that query in the current query as an in-line view (See @Tom's example).

[Tecla1]

... INNER JOIN (select a,c,c from x.y) q_ultimi_ia_3 ON ...

What mean "x.y"

Tnks

Tecla 

[Tom]

?? Is was just and example SQL query since you did show what the missing query was.

So 

select a,b,c from x.y

Says to retrieve the variables A B and C from the dataset Y in the library pointed to by the libref X.

One thing you will definitely need to change from Microsoft syntax is the way you refer to names (tables or datasets, columns or variables, etc).  You cannot use those square brackets in SAS code.  

So something like giudizi_audit.[gia_val] in your example will need to be converted to normal syntax like giudizi_audit.gia_val .

[Tecla1]

Goodmorning, many tnks to all for your kindly help.
Tecla

[LinusH]

I would test a an AI tool such as ChatGPT as a start. But then of course the outcome has to be adjusted, tested and verified by someone (you?).
Please post an example of a query you wish to translate and we might be able to give some general guidelines.

[Tecla1]

I know, but I'm not so well in SaS...
Tnks a lot:
Tecla