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 ?
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.
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.
Hello ,
Thanks for the detection of error. I correct my parameter .
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: