Définir une fonction que l'on peut appeler dans une PROC SQL [Résolu]

[klopi]

Bonjour,

existe-t-il un moyen dans SAS 9.2 de définir une fonction que l'on peut appeler à l'intérieur d'une PROC SQL ?

Il est possible de créer une fonction avec la PROC FCMP, et de l'utiliser dans un data step; par contre, la même fonction "is not supported in PROC SQL".

Exemple de ce que je souhaite réaliser avce la proc sql :

Code :

1
2
3
proc sql;
SELECT champ1, champ2, ma_fonction(champ1,champ2) AS nouveau_champ
FROM donnees;

L'équivalent avec un data step, réalisable via la PROC FCMP, est le suivant :

Code :

1
2
3
4
DATA donnees; 
SET donnees;
nouveau_champ=ma_fonction(champ1,champ2);
run;

Merci.

[MEGAMIND2]

C'est quoi l'intérêt de passer par une fonction pour une tâche aussi simple?
à moins que ça soit pour ta culture générale

[klopi]

Sur une application déjà en production, mais qui ne tourne pas sous SAS, je dispose de nombreuses requêtes SQL. Je dois néanmoins faire passer l'applicatif sous SAS.
Plutôt que d'utiliser le data step, je préfère donc utiliser (quasiment) telles quelles les requêtes existantes, ce qui réduirait les coûts de développement et les risques d'erreurs. En effet, l'exemple que j'ai fourni dans le premier message, d'une requête SQL très simple -courte et une seule jointure-, n'est pas représentatif de la complexité des requêtes que je dois faire basculer d'un applicatif à l'autre, lesquelles peuvent comprendre 10.000 signes et 20 jointures.

[datametric]

J'imagine que ce sont des fonctions spécifiques à ton SGBD et qui n'existe pas dans SAS ?

dans ce cas tu peux faire un sous-Select et copier/coller les requêtes dedans.

Exemple illustratif avec DB2 mais ca marche avec teradata, oracle etc...

Code :

1
2
3
4
5
6
7
8
proc SQL.
Connect TO DB2 (user= ...);
 
SELECT * FROM CONNECTION TO DB2 
(
SELECT * FROM TABLE HAVING max(c) fetch row_number 10
);
quit;

[klopi]

Réponse
L'applicatif existant bascule sur du 100% SAS.
Le SGBD actuel ne devant plus être utilisé à la cible, les fonctions définies par voie de développement sur ce SGBD ne pourront plus être utilisées.

Etat de la réflexion
Les logiciels mis à ma disposition changent, mais le cahier des charges fonctionnel reste le même. Comme les différents logiciels font a priori grosso modo la même chose (notamment leur langage SQL est proche), l'idée est de reprendre les développements existants et de les basculer le plus simplement possible.
Je n'ai pas trouvé par moi-même l'équivalent dans SAS de la possibilité offertes par d'autres SGBD de définir ses propres fonctions insérables dans du SQL.
Si je n'ai pas trouvé, est-ce parce que cela n'existe pas ?
J'ai en tout cas noté les alternatives :
-continuer à utiliser un SGBD autre que SAS (mais ce n'est pas le choix du client)
-utiliser le data step + proc fcmp (inconvénient que les développements s'annoncent fastidieux et en cadrent pas avec les délais prévus)

[datametric]

donc c'est bien de fonctions de SGBD dont tu parlais.

dans ce cas, je crains que tu doives revoir la conception des applicatifs.

Le chiffrage de ton forfait n'en n'avait pas tenu compte ?

As-tu des fonctions qui sont réellement spécifiques qui amèneraient une réécriture complexe en SAS ?

[olivier.decourt]

+1 Datametric
Je pense que le plus simple serait d'essayer, avec FCMP, de rédévelopper l'équivalent des fonctions natives de ton ancien logiciel et de s'en servir ensuite dans une proc SQL.
A priori, rien dans la doc ni dans mon expérience n'interdit d'utiliser les fonctions de FCMP dans la proc SQL.

[s_a_m]

Envoyé par olivier.decourt

+1 Datametric
Je pense que le plus simple serait d'essayer, avec FCMP, de rédévelopper l'équivalent des fonctions natives de ton ancien logiciel et de s'en servir ensuite dans une proc SQL.
A priori, rien dans la doc ni dans mon expérience n'interdit d'utiliser les fonctions de FCMP dans la proc SQL.

Juste un avis personnel ( celà ce veut en aucun cas dire que ce que vous dites est faux).
Le mieux c'est de ne pas passer par le FCMP, car il faut penser à la maintenance corrective et évolutive. Maintenir une application est sauvant plus difficile que de la créer, surtout s’elle est très mal documentée.

SAS offre déjà pas mal de fonctions et de procédures. Je ne vois tout de même pas pourquoi il faut en créer de nouvelles sauf si c’est pour compliquer la programmation ou qu’on n’a pas le choix.

[datametric]

+1

[klopi]

olivier.decourt a écrit

Je pense que le plus simple serait d'essayer, avec FCMP, de rédévelopper l'équivalent des fonctions natives de ton ancien logiciel et de s'en servir ensuite dans une proc SQL.
A priori, rien dans la doc ni dans mon expérience n'interdit d'utiliser les fonctions de FCMP dans la proc SQL.

Mon échec à utiliser, dans une PROC SQL, une fonction créée avec la PROC FCMP, est l'objet de ce fil de discussion.
Le message d'erreur est "(ma fonction) is not supported in PROC SQL" -la même fonction peut être appelée dans un data step.
Que faire ? Soit trouver une alternative à la PROC FCMP, soit parvenir à utiliser la PROC FCMP sans générer le message d'erreur ci-dessus.

A Datametric
La solution-compromis que j'imagine est un mixte entre PROC SQL et Data Step. C'est à dire que, au lieu d'écrire une seule requête SQL, complexe et contenant plusieurs appels à des fonctions, obtenir le même résultat mais par étapes successives, en utilisant le plus pertinent de la PROC SQL ou du Data Step pour chaque étape. Ainsi, la PROC SQL est utilisée pour reprendre au possible l'existant, et le Data Step, conjointement à la PROC FCMP, est utilisée lorsque c'est nécessaire pour avoir accès aux fonctions.

A s_a_m
De mon point de vue, l'utilisation d'une fonction améliore la lisibilité du code (factorisation, décomposition d'un traitement complexe en étapes élémentaires faciles d'accès).

[bahraoui]

Bonjour,

Je pense qu'une fonction fcmp peut fonctionner en étape data et non en SQL, vu qu'une fonction s'applique sur les valeurs de la colonne en SQL alors que la même fonction s'applique sur un enregistrement d'une étape data.

Exemple :

La fonction max en sql s'applique sur une colonne alors qu'en étape data elle s'applique sur plusieurs variables de la même observation!!

Je pense qu'il faut prendre en considération ces éléments si on développe nos proposes fonctions.

J'espère que c'est clair.

Cordialement
Salah

[bahraoui]

J'ai trouvé un exemple dans lequel on définit une fonction avec fcmp et on l'utilise dans une proc SQL.

http://www.sascommunity.org/wiki/Com...riables_in_SAS

[s_a_m]

Envoyé par klopi

A s_a_m
De mon point de vue, l'utilisation d'une fonction améliore la lisibilité du code (factorisation, décomposition d'un traitement complexe en étapes élémentaires faciles d'accès).

Ce que je voulais dire, le mieux est d'utiliser ce qui existe déjà comme fonctions et procédures. Et je suis presque certain que ce qu'existe déjà comme fonctions, procédures SAS pourra répondre à 99% à ton besoin (par contre sa dépend de ce que t’as comme modules, solution et architecture et…).
T’inquiète je ne critique pas ta démarche et je trouve même intelligent le fait de créer ces propres fonctions (mais il faut penser à bien les commenter).

[olivier.decourt]

Envoyé par klopi

Mon échec à utiliser, dans une PROC SQL, une fonction créée avec la PROC FCMP, est l'objet de ce fil de discussion.
Le message d'erreur est "(ma fonction) is not supported in PROC SQL" -la même fonction peut être appelée dans un data step.
Que faire ? Soit trouver une alternative à la PROC FCMP, soit parvenir à utiliser la PROC FCMP sans générer le message d'erreur ci-dessus.

J'en avais bien conscience, et ma question implicite (qui aurait dû être explicite) est : mais que fait donc ta fonction qui n'est pas supporté en SQL ? Elle utilise un numéro d'observation ? Un FIRST ou un LAST ?
En règle générale, et comme l'exemple proposé par Salah le rappelle, on peut utiliser les fonctions créées par FCMP dans tous les contextes.

Olivier

[datametric]

En règle générale, et comme l'exemple proposé par Salah le rappelle, on peut utiliser les fonctions créées par FCMP dans tous les contextes.

En 9.2

mais que fait donc ta fonction qui n'est pas supporté en SQL ? Elle utilise un numéro d'observation ? Un FIRST ou un LAST ?

Cf. ma réponse plus haut. Ce sont des fonctions du SGBD et non du SQL "générique"

[bahraoui]

Bonjour,

Est ce que c'est possible de nous donner un exemple de fonction, cela nous permettra de trouver des solutions...?

Cordialement
Salah

[olivier.decourt]

Envoyé par datametric

Cf. ma réponse plus haut. Ce sont des fonctions du SGBD et non du SQL "générique"

Stéphane, pas uniquement ! Puisque Klopi a déjà fait des essais et qu'il nous dit que

Le message d'erreur est "(ma fonction) is not supported in PROC SQL" -la même fonction peut être appelée dans un data step.

Comme dit Salah, effectivement, on veut savoir ce que fait/font cette/ces mystérieuse(s) fonction(s) pour réfléchir à des solutions.

Et en 9.2 effectivement, puisque Klopi a pu tester avec succès dans une étape Data (en 9.1 on est plus ou moins cantonné aux procs FCMP et MODEL).

[klopi]

Solution en synthèse

Une fonction créée avec la PROC FCMP ne réagit pas de la même façon aux conversions implicites des paramètres, lorsqu'elle est employée dans une PROC SQL ou dans un Data Step. Appelée dans une PROC SQL, elle génère une erreur, mais pas dans le Data Step.
Ainsi, après rectification de la fonction que j'avais initialement écrite, cette fonction est utilisée avec succès dans une proc SQL.
Merci pour vos contributions.


Cheminement

Merci bahraoui pour ton lien, contenant un exemple d'utilisation de la PROC FCMP avec une PROC SQL. Dans cet exemple, la fonction créée accepte en paramètres des champs d'une table présente dans la clause from, comprend des branchements conditionnels et des boucles.

Comme les fonctions que je souhaite créer ont ce même niveau de complexité, mais rien de plus, il devient clair que le message d'erreur que j'obtiens tient intégralement à des spécificités anecdotiques de ma fonction.

J'ai donc cherché à déterminer quelle est cette spécificité, en la mettant en évidence sur des fonctions simplifiées. Voici le résultat obtenu :

La fonction Simplissime ci-dessous produit le résultat escompté dans un data simple, générant toutefois la note suivante :

Code :

NOTE: Character VALUES have been converted TO numeric

Cette même fonction échoue cependant dans une PROC SQL, avec les deux messages d'erreur suivant :

Code :

1
2
ERROR: FUNCTION SIMPLISSIME requires a numeric expression AS argument 1.
ERROR: The SIMPLISSIME FUNCTION IS NOT supported IN PROC SQL, it IS only valid within the DATA step.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
options cmplib=work.fonctions;
proc fcmp outlib=work.fonctions.fct;
   FUNCTION simplissime(param);
      RETURN(param);
   endsub;
run;
DATA work.x;
   INFILE datalines;
   length x $1.;
   input x;
   datalines;
   1
   2
   ;
run;
DATA work.y_data;
   SET work.x;
   y=simplissime(x);
run;
proc sql;
CREATE TABLE work.y_sql AS
SELECT x, simplissime(x) AS y
FROM work.x;