topic Re: Da Access SQL a SaS in SAS Programming

Da Access SQL a SaS

Tecla1 — Tue, 13 May 2025 09:28:17 GMT

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

Re: Da Access SQL a SaS

Kurt_Bremser — Tue, 13 May 2025 11:09:07 GMT

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.

Re: Da Access SQL a SaS

LinusH — Tue, 13 May 2025 12:36:38 GMT

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.

Re: Da Access SQL a SaS

Tecla1 — Tue, 13 May 2025 12:48:09 GMT

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

Re: Da Access SQL a SaS

Tecla1 — Tue, 13 May 2025 12:48:59 GMT

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

Re: Da Access SQL a SaS

Tom — Tue, 13 May 2025 13:33:39 GMT

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

Re: Da Access SQL a SaS

Tecla1 — Tue, 13 May 2025 13:40:18 GMT

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

Re: Da Access SQL a SaS

LinusH — Tue, 13 May 2025 13:53:52 GMT

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

Re: Da Access SQL a SaS

Tecla1 — Tue, 13 May 2025 14:01:31 GMT

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

What mean "x.y"

Tnks

Tecla

Re: Da Access SQL a SaS

Tom — Tue, 13 May 2025 14:15:35 GMT

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

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 .

Re: Da Access SQL a SaS

Tecla1 — Mon, 19 May 2025 06:37:57 GMT

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