Hello , I have tried this SAS code : options compress=yes sortsize=max ;
/* bornage des dates pour les intervals de la date initiale de création */
%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 ; ');
/* comptage détaillé en fonction de la nature d'evenement */
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; When I use CALL execute the same sql query is invalid ... an "OR" condition is tranformed in " IN " condition . I don't know which is this problem or delirium ??? 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 Can you give me your opinion to solve this problem . Thnaks for your help ?
... View more
