condition OR transformed in a condition IN with call execute VS proc sql

[azertyuiop]

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 ?

[Kurt_Bremser · Accepted solution]

Your call execute version misses the dt extension after the quoted strings.

STRONG HINT: do not store the formatted values in your macro variables, but the raw values. You only need formatted values if you want to display them, for conditions they are not necessary and usually a PITA, as you just experienced.

data _null_;
call symput('bornemin',put('01JAN2016:00:00:00'dt,best.));
call symput('bornemax',put('30APR2016:00:00:00'dt,best.));
run;

Now you can use the macro variables without any quotes or extensions in your condition.

[azertyuiop]

Hello ,

Thanks for the detection of error. I correct my parameter .