Condition OR qui se transforme en condition IN avec call execute VS proc sql

**tanaka59** · 26/12/2017, 12h21

Bonjour,

J'ai testé ce code SAS :

Code sas :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
options compress=yes sortsize=max ;
 
%let bornemin = '01JAN2016:00:00:00';
%let bornemax = '30APR2016:00:00:00';
 
data _null_ ;
 
d_effet_debut=&bornemin;
d_effet_fin=&bornemax;
 
call execute(' proc sql ; ');
 
call execute(' create table resultat as ');
 
call execute(' select db_dossier.no_police , db_contrat.lp_contrat as type_sous , db_dossier.lp_etat_doss as etat_sousc , '); 
call execute(' db_dossier.is_dossier , db_dossier.d_effet as effet_cont , db_contrat.lp_fiscalite ');
call execute(' from PEGASE.db_dossier , PEGASE.db_contrat ');
call execute(' where db_dossier.d_effet >="'!!d_effet_debut!!'" ' );
call execute(' and db_dossier.d_effet <"'!!d_effet_fin!!'" ');
call execute(' and ( db_dossier.lp_etat_doss in ( "OPEN" , "VALID" , "ANNUL" , "A30" , "RT" , "DCASS" ) ) ');
call execute(' and db_contrat.lp_contrat not like "TFERT" ' );
call execute(' and db_dossier.is_dossier = db_contrat.is_dossier ' );
call execute(' and ( ( db_contrat.lp_fiscalite not in ( "PERP" , "MADEL" ) ) or db_contrat.lp_fiscalite is NULL ) ' );
call execute(' order by db_dossier.no_police  ' );
call execute(' ; ');
 
call execute(' run ; ');
 
run;
 
 proc sql ;
 
create table resultat as 
 
select db_dossier.no_police , db_contrat.lp_contrat as type_sous , db_dossier.lp_etat_doss as etat_sousc ,
db_dossier.is_dossier , db_dossier.d_effet as effet_cont , db_contrat.lp_fiscalite 
from PEGASE.db_dossier , PEGASE.db_contrat 
where db_dossier.d_effet >='01JAN2016:00:00:00'dt 
 and db_dossier.d_effet <'30APR2016:00:00:00'dt
and ( db_dossier.lp_etat_doss in ( "OPEN" , "VALID" , "ANNUL" , "A30" , "RT" , "DCASS" ) ) 
and db_contrat.lp_contrat not like "TFERT" 
 and db_dossier.is_dossier = db_contrat.is_dossier 
and ( ( db_contrat.lp_fiscalite not in ( "PERP" , "MADEL" ) ) or db_contrat.lp_fiscalite is null ) 
order by db_dossier.no_police  
; 
 
run;

Le call execute transforme un "OR en "IN" ??? C'est quoi ce délire ?

Depuis quand une instruction change de statut par enchantement ???

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 options compress=yes sortsize=max ;
24
25 /* bornage des dates pour les intervals de la date initiale de création */
26
27 %let bornemin = '01JAN2016:00:00:00';
28 %let bornemax = '30APR2016:00:00:00';
29

30 data _null_ ;
31
32 d_effet_debut=&bornemin;
33 d_effet_fin=&bornemax;
34
35 call execute(' proc sql ; ');
36
37 /* comptage détaillé en fonction de la nature d'evenement */
38
39 call execute(' create table resultat as ');
40
41 call execute(' select db_dossier.no_police , db_contrat.lp_contrat as type_sous , db_dossier.lp_etat_doss as etat_sousc ,
41 ! ');
42 call execute(' db_dossier.is_dossier , db_dossier.d_effet as effet_cont , db_contrat.lp_fiscalite ');
43 call execute(' from PEGASE.db_dossier , PEGASE.db_contrat ');
44 call execute(' where db_dossier.d_effet >="'!!d_effet_debut!!'" ' );
45 call execute(' and db_dossier.d_effet <"'!!d_effet_fin!!'" ');
46 call execute(' and ( db_dossier.lp_etat_doss in ( "OPEN" , "VALID" , "ANNUL" , "A30" , "RT" , "DCASS" ) ) ');
47 call execute(' and db_contrat.lp_contrat not like "TFERT" ' );
48 call execute(' and db_dossier.is_dossier = db_contrat.is_dossier ' );
49 call execute(' and ( ( db_contrat.lp_fiscalite not in ( "PERP" , "MADEL" ) ) or db_contrat.lp_fiscalite is NULL ) ' );
50 call execute(' order by db_dossier.no_police ' );
51 call execute(' ; ');
52
53 call execute(' run ; ');
54
2 The SAS System 11:27 Tuesday, December 26, 2017

55 run;

NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

NOTE: CALL EXECUTE generated line.
1 + proc sql ;
2 + create table resultat as
3 + select db_dossier.no_police , db_contrat.lp_contrat as type_sous , db_dossier.lp_etat_doss as etat_sousc ,
4 + db_dossier.is_dossier , db_dossier.d_effet as effet_cont , db_contrat.lp_fiscalite
5 + from PEGASE.db_dossier , PEGASE.db_contrat
6 + where db_dossier.d_effet >="01JAN2016:00:00:00"
7 + and db_dossier.d_effet <"30APR2016:00:00:00"
8 + and ( db_dossier.lp_etat_doss in ( "OPEN" , "VALID" , "ANNUL" , "A30" , "RT" , "DCASS" ) )
9 + and db_contrat.lp_contrat not like "TFERT"
10 + and db_dossier.is_dossier = db_contrat.is_dossier
11 + and ( ( db_contrat.lp_fiscalite not in ( "PERP" , "MADEL" ) ) or db_contrat.lp_fiscalite is NULL )
12 + order by db_dossier.no_police
13 + ;
ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
14 + run ;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
56
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds

57 proc sql ;

58
59 create table resultat as
60
61 select db_dossier.no_police , db_contrat.lp_contrat as type_sous , db_dossier.lp_etat_doss as etat_sousc ,
62 db_dossier.is_dossier , db_dossier.d_effet as effet_cont , db_contrat.lp_fiscalite
63 from PEGASE.db_dossier , PEGASE.db_contrat
64 where db_dossier.d_effet >='01JAN2016:00:00:00'dt
65 and db_dossier.d_effet <'30APR2016:00:00:00'dt
66 and ( db_dossier.lp_etat_doss in ( "OPEN" , "VALID" , "ANNUL" , "A30" , "RT" , "DCASS" ) )
67 and db_contrat.lp_contrat not like "TFERT"
68 and db_dossier.is_dossier = db_contrat.is_dossier
69 and ( ( db_contrat.lp_fiscalite not in ( "PERP" , "MADEL" ) ) or db_contrat.lp_fiscalite is null )
70 order by db_dossier.no_police
71 ;
NOTE: Compressing data set WORK.RESULTAT increased size by 33.33 percent.
Compressed is 4 pages; un-compressed would require 3 pages.
NOTE: Table WORK.RESULTAT created, with 3199 rows and 6 columns.

72
73 run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
74

Je ne vois pas pourquoi la requête fonctionne en mode B mais pas en mode A ... j'ai déjà fait d'autres requête et ce problème n'est jamais apparu . J'ai vraiment l'impression que les messages d'erreurs sont la pour se foutre de la gueule du monde

Ba voyons demain en java les " ; " vont devenir des virgules par enchantement et les balise du html vont devenir des slachs

Merci de me donner votre avis