Desktop productivity for business analysts and programmers

data _null_ file with dynamic exit file

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

data _null_ file with dynamic exit file

Hello , Good morning ,

 

I own a table "refec9" with this fields :

 

numero_police

application

flux

cause_evenement

anomalie

date_creation_rejet

acteur

statut

analyse_gestion

analyse_moe

date_derniere_modif

lid

rolper

numper

I want create 8 tables in function of the fields "acteur" . This field can own 8 differents value in the table refec9.

 

I have built this sas code to export in SAS by data _null_ with file :

 

data _null_;

set work.refec9;

mois = month(input("&sysdate9",date9.));
annee = year(input("&sysdate9",date9.));
jour  = day(input("&sysdate9",date9.));
call symput('mois',compress(put(mois,Z2.)));
call symput('annee',compress(put(annee,best.)));
call symput('jour',compress(put(jour,Z2.)));
call symputx('lien','\\batd0\bases_rad\ficovie\');
call symputx('fichier','_ACTEURXXX');
call symputx('acteur00','MOA Reglementaire');
call symputx('acteur01','MOE FICOVIE');
call symputx('acteur02','Gestion Pegase');
call symputx('acteur03','Gestion 8X');
call symputx('acteur04','Gestion PTV');
call symputx('acteur05','Gestion Espece');
call symputx('acteur06','Gestion Obseques');
call symputx('acteur07','Gestion BIGBEN');
call symputx('header','numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper');

if FIRST.acteur then do;
put &header;
   if refec9.acteur=&acteur00 then do;
   file "&lien&annee&mois&jour&acteur00..xlsx" encoding="UTF-8" ;
   put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper;
   end;
   else
   if refec9.acteur=&acteur01 then do;
   file "&lien&annee&mois&jour&acteur01..xlsx" encoding="UTF-8" ;
   put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper;
   end;
end;

run;

In the log I can see this :

 

24         data _null_;
25         
26         set work.refec9;
27         
28         mois = month(input("&sysdate9",date9.));
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 23NOV2017
29         annee = year(input("&sysdate9",date9.));
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 23NOV2017
30         jour  = day(input("&sysdate9",date9.));
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 23NOV2017
31         call symput('mois',compress(put(mois,Z2.)));
32         call symput('annee',compress(put(annee,best.)));
33         call symput('jour',compress(put(jour,Z2.)));
34         call symputx('lien','\\batd0\bases_rad\ficovie\');
35         call symputx('fichier','_ACTEURXXX');
36         call symputx('acteur00','MOA Reglementaire');
37         call symputx('acteur01','MOE FICOVIE');
38         call symputx('acteur02','Gestion Pegase');
39         call symputx('acteur03','Gestion 8X');
40         call symputx('acteur04','Gestion PTV');
41         call symputx('acteur05','Gestion Espece');
42         call symputx('acteur06','Gestion Obseques');
43         call symputx('acteur07','Gestion BIGBEN');
44         call symputx('header','numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut
44       ! analyse_gestion analyse_moe date_derniere_modif lid rolper numper');
45         
46         if FIRST.acteur then do;
47         put &header;
               _
               22
WARNING: Apparent symbolic reference HEADER not resolved.
2                                                          The SAS System                          09:23 Thursday, November 23, 2017

48            if refec9.acteur=&acteur00 then do;
                 _____________
                 557
ERROR: DATA STEP Component Object failure.  Aborted during the COMPILATION phase.
ERROR 22-322: Expecting a name.  

ERROR 557-185: Variable refec9 is not an object.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
SYMBOLGEN:  Macro variable ACTEUR00 resolves to MOA Reglementaire
SYMBOLGEN:  Macro variable LIEN resolves to \\batd0\bases_rad\ficovie\
SYMBOLGEN:  Macro variable ANNEE resolves to 2017
SYMBOLGEN:  Macro variable MOIS resolves to 11
SYMBOLGEN:  Macro variable JOUR resolves to 23
SYMBOLGEN:  Macro variable ACTEUR00 resolves to MOA Reglementaire
49            file "&lien&annee&mois&jour&acteur00..xlsx" encoding="UTF-8" ;
50            put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion
50       ! analyse_moe date_derniere_modif lid rolper numper;
51            end;
52            else
53            if refec9.acteur=&acteur01 then do;
SYMBOLGEN:  Macro variable ACTEUR01 resolves to MOE FICOVIE
SYMBOLGEN:  Macro variable LIEN resolves to \\batd0\bases_rad\ficovie\
SYMBOLGEN:  Macro variable ANNEE resolves to 2017
SYMBOLGEN:  Macro variable MOIS resolves to 11
SYMBOLGEN:  Macro variable JOUR resolves to 23
SYMBOLGEN:  Macro variable ACTEUR01 resolves to MOE FICOVIE
54            file "&lien&annee&mois&jour&acteur01..xlsx" encoding="UTF-8" ;
55            put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion
55       ! analyse_moe date_derniere_modif lid rolper numper;
56            end;
57         end;
58         
59         run;
60         
61         GOPTIONS NOACCESSIBLE;
62         %LET _CLIENTTASKLABEL=;
63         %LET _CLIENTPROJECTPATH=;
64         %LET _CLIENTPROJECTNAME=;
65         %LET _SASPROGRAMFILE=;
66         
67         ;*';*";*/;quit;run;
68         ODS _ALL_ CLOSE;
69         
70         
71         QUIT; RUN;

SAS refuse to read when there is "table.field" in the condition , and refuse to read several variables like "header" or "acteurXX" to divide the table in several file.

 

I have use this documentation : http://www.users.miamioh.edu/baileraj/classes/sta402/FALL-2007/handouts/week-09-02nov07-write-transf... , https://thesasreference.wordpress.com/tag/data-_null_/ , http://www2.sas.com/proceedings/sugi27/p061-27.pdf .

 

I don't why SAS refuse to accept variable like "header" or "acteurxx"  in condition ??? Smiley Sad

 

If you have an idea you can give it Smiley Happy

 

 


Accepted Solutions
Solution
‎11-28-2017 05:14 AM
Super User
Posts: 9,548

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

I repeat from a previous post of mine:

You CANNOT, I repeat, CANNOT, use a macro variable with (eg) &acteur00 when you create this macro variable in the same step with call symputx, because

CALL SYMPUTX WORKS WHILE THE DATA STEP RUNS, BUT &ACTEUR00 IS RESOLVED LONG BEFORE THAT WHEN THE DATA STEP IS COMPILED!!!

 

I hope I made myself clear enough now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 22,818

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

You're attempting to use FIRST logic, but you don't have a BY statement. 

 

And note that if you create a macro variable in one step you can't use it in the same step - but I'm not sure why you'd need to either, why not just use it as text? 

Or you could separate the steps. 

 

 

Super User
Posts: 9,548

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

The macro variable reference (&) is resolved while the data step code is fetched, while call symput is executed later when the step runs.

Set the macro variables before the data step us compiled with %let.

 

You also use first. without a by statement.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

Hello , Good morning ,

 

You're attempting to use FIRST logic, but you don't have a BY statement.

 

To correct this error I have decided to add this proc :

 

 

proc sort data=work.refec9; by acteur;

 

You're attempting to use FIRST logic, but you don't have a BY statement.

 

And note that if you create a macro variable in one step you can't use it in the same step - but I'm not sure why you'd need to either, why not just use it as text?

Or you could separate the steps.

 

I have grouped all instruction in one data _null_ step in using a conditionnal structure : "  if/else " .

 

Or you could separate the steps.

 

For you If I must divide my table in 8 outing files we must found 8 data _null_ instruction to export ?

 

 

 

 

 

 

 

 

 

 

 

 

 

Super User
Posts: 9,548

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

If you use first. or last., you must have a respective by statement in the SAME step.

 

If you want to dynamically change output files in a data step, use the filevar= option in the file statement.

 

See http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000171874.htm, example 5.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: data _null_ file with dynamic exit file

[ Edited ]
Posted in reply to KurtBremser

I have rebuilt the program like this :

 

data _null_;

mois = month(input("&sysdate9",date9.));
annee = year(input("&sysdate9",date9.));
jour  = day(input("&sysdate9",date9.));
call symput('mois',compress(put(mois,Z2.)));
call symput('annee',compress(put(annee,best.)));
call symput('jour',compress(put(jour,Z2.)));
call symputx('lien','\\batd0\bases_rad\ficovie\');
call symputx('fichier','_ACTEURXXX');
call symputx('extension','.xlsx');
run;
 
%put &annee &mois &jour &lien &fichier &extension ;


proc sort data=work.refec9; by acteur;

data _null_;

set work.refec9;

call symputx('acteur00','MOA Reglementaire');
call symputx('acteur01','MOE FICOVIE');
call symputx('acteur02','Gestion Pegase');
call symputx('acteur03','Gestion 8X');
call symputx('acteur04','Gestion PTV');
call symputx('acteur05','Gestion Espece');
call symputx('acteur06','Gestion Obseques');
call symputx('acteur07','Gestion BIGBEN');
call symputx('header','numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper');
/*
%let lien=\\batd0\bases_rad\ficovie\;
%let fichier='_ACTEURXXX';
%let acteur00='MOA Reglementaire';
%let acteur01='MOE FICOVIE';
%let acteur02='Gestion Pegase';
%let acteur03='Gestion 8X';
%let acteur04='Gestion PTV';
%let acteur05='Gestion Espece';
%let acteur06='Gestion Obseques';
%let acteur07='Gestion BIGBEN';
%let header='numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper';
*/

by acteur;

if FIRST.acteur then do;
title &header;
   if acteur=&acteur00 then do;
   file "&lien&annee&mois&jour&acteur00&extension" filevar=refec9 mod;
   put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper;
   end;
   else
   if acteur=&acteur01 then do;
   file "&lien&annee&mois&jour&acteur01&extension" filevar=refec9 mod;
   put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper;
   end;
end;

run;

SAS can't be read the variable "acteurXX" if there is a space on the value ... But in my table variable own a space it's a constraint. Each time the second word after the space it's recognized by SAS, and SAS underline this like an error .

 

The instruction file refuse all paramaters that I can give . I don't see why this function refuse all parameters ???!!!

 

data _null_;

set work.refec9;

call symputx('acteur00','MOA Reglementaire');
call symputx('acteur01','MOE FICOVIE');
call symputx('acteur02','Gestion Pegase');
call symputx('acteur03','Gestion 8X');
call symputx('acteur04','Gestion PTV');
call symputx('acteur05','Gestion Espece');
call symputx('acteur06','Gestion Obseques');
call symputx('acteur07','Gestion BIGBEN');
call symputx('header','numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper');
/*
%let lien=\\batd0\bases_rad\ficovie\;
%let fichier='_ACTEURXXX';
%let acteur00='MOA Reglementaire';
%let acteur01='MOE FICOVIE';
%let acteur02='Gestion Pegase';
%let acteur03='Gestion 8X';
%let acteur04='Gestion PTV';
%let acteur05='Gestion Espece';
%let acteur06='Gestion Obseques';
%let acteur07='Gestion BIGBEN';
%let header='numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper';
*/

by acteur;

if FIRST.acteur then do; 
title &header;
   if acteur=&acteur00 then do; 
   file "&lien&annee&mois&jour&acteur00&extension" filevar=refec9 mod;
   put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper;
   end;
   else 
   if acteur=&acteur01 then do; 
   file "&lien&annee&mois&jour&acteur01&extension" filevar=refec9 mod;
   put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper;
   end;
end;

run;

 

 SAS log :

 

 NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         data _null_;
25         
26         mois = month(input("&sysdate9",date9.));
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 24NOV2017
27         annee = year(input("&sysdate9",date9.));
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 24NOV2017
28         jour  = day(input("&sysdate9",date9.));
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 24NOV2017
29         call symput('mois',compress(put(mois,Z2.)));
30         call symput('annee',compress(put(annee,best.)));
31         call symput('jour',compress(put(jour,Z2.)));
32         /**/
33         call symputx('lien','\\batd0\bases_rad\ficovie\');
34         call symputx('fichier','_ACTEURXXX');
35         call symputx('extension','.xlsx');
36         run;

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

37         
38         %put &annee &mois &jour &lien &fichier &extension ;
SYMBOLGEN:  Macro variable ANNEE resolves to 2017
SYMBOLGEN:  Macro variable MOIS resolves to 11
SYMBOLGEN:  Macro variable JOUR resolves to 24
SYMBOLGEN:  Macro variable LIEN resolves to \\batd0\bases_rad\ficovie\
SYMBOLGEN:  Macro variable FICHIER resolves to _ACTEURXXX
SYMBOLGEN:  Macro variable EXTENSION resolves to .xlsx
2017 11 24 \\batd0\bases_rad\ficovie\ _ACTEURXXX .xlsx
39         
2                                                          The SAS System                            09:00 Friday, November 24, 2017

40         GOPTIONS NOACCESSIBLE;
41         %LET _CLIENTTASKLABEL=;
42         %LET _CLIENTPROJECTPATH=;
43         %LET _CLIENTPROJECTNAME=;
44         %LET _SASPROGRAMFILE=;
45         
46         ;*';*";*/;quit;run;
47         ODS _ALL_ CLOSE;
48         
49         
50         QUIT; RUN;

 

 and the second part :

 

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         proc sort data=work.refec9; by acteur;
25         

NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
26         data _null_;

27         
28         set work.refec9;
29         
30         call symputx('acteur00','MOA Reglementaire');
31         call symputx('acteur01','MOE FICOVIE');
32         call symputx('acteur02','Gestion Pegase');
33         call symputx('acteur03','Gestion 8X');
34         call symputx('acteur04','Gestion PTV');
35         call symputx('acteur05','Gestion Espece');
36         call symputx('acteur06','Gestion Obseques');
37         call symputx('acteur07','Gestion BIGBEN');
38         call symputx('header','numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut
38       ! analyse_gestion analyse_moe date_derniere_modif lid rolper numper');
39         /*
40         %let lien=\\batd0\bases_rad\ficovie\;
41         %let fichier='_ACTEURXXX';
42         %let acteur00='MOA Reglementaire';
43         %let acteur01='MOE FICOVIE';
44         %let acteur02='Gestion Pegase';
45         %let acteur03='Gestion 8X';
46         %let acteur04='Gestion PTV';
47         %let acteur05='Gestion Espece';
48         %let acteur06='Gestion Obseques';
2                                                          The SAS System                            09:00 Friday, November 24, 2017

49         %let acteur07='Gestion BIGBEN';
50         %let header='numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion
50       ! analyse_moe date_derniere_modif lid rolper numper';
51         */
52         
53         by acteur;
54         
55         if FIRST.acteur then do;
SYMBOLGEN:  Macro variable HEADER resolves to numero_police application flux cause_evenement anomalie date_creation_rejet acteur
            statut analyse_gestion analyse_moe date_derniere_modif lid rolper numper
56         title &header;
SYMBOLGEN:  Macro variable ACTEUR00 resolves to MOA Reglementaire
SYMBOLGEN:  Macro variable LIEN resolves to \\batd0\bases_rad\ficovie\
SYMBOLGEN:  Macro variable ANNEE resolves to 2017
SYMBOLGEN:  Macro variable MOIS resolves to 11
SYMBOLGEN:  Macro variable JOUR resolves to 24
SYMBOLGEN:  Macro variable ACTEUR00 resolves to MOA Reglementaire
SYMBOLGEN:  Macro variable EXTENSION resolves to .xlsx
ERROR: A Physical file reference (i.e. "PHYSICAL FILE REFERENCE" ) or an aggregate file storage reference (i.e. AGGREGATE(MEMBER) )
       reference cannot be used with the FILEVAR= option.
57            if acteur=&acteur00 then do;
NOTE: Line generated by the macro variable "ACTEUR00".
57         MOA Reglementaire
               _____________
               388
               76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

58            file "&lien&annee&mois&jour&acteur00&extension" filevar=refec9 mod;
59            put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion
59       ! analyse_moe date_derniere_modif lid rolper numper;
SYMBOLGEN:  Macro variable ACTEUR01 resolves to MOE FICOVIE
60            end;
61            else
SYMBOLGEN:  Macro variable LIEN resolves to \\batd0\bases_rad\ficovie\
SYMBOLGEN:  Macro variable ANNEE resolves to 2017
SYMBOLGEN:  Macro variable MOIS resolves to 11
SYMBOLGEN:  Macro variable JOUR resolves to 24
SYMBOLGEN:  Macro variable ACTEUR01 resolves to MOE FICOVIE
SYMBOLGEN:  Macro variable EXTENSION resolves to .xlsx
62            if acteur=&acteur01 then do;
ERROR: A Physical file reference (i.e. "PHYSICAL FILE REFERENCE" ) or an aggregate file storage reference (i.e. AGGREGATE(MEMBER) )
       reference cannot be used with the FILEVAR= option.
NOTE: Line generated by the macro variable "ACTEUR01".
62         MOE FICOVIE
               _______
               388
               76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

63            file "&lien&annee&mois&jour&acteur01&extension" filevar=refec9 mod;
64            put numero_police application flux cause_evenement anomalie date_creation_rejet acteur statut analyse_gestion
64       ! analyse_moe date_derniere_modif lid rolper numper;
65            end;
3                                                          The SAS System                            09:00 Friday, November 24, 2017

              ___
              161
66         end;
           ___
           161
ERROR 161-185: No matching DO/SELECT statement.

67         
68         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

69         
70         
71         GOPTIONS NOACCESSIBLE;
72         %LET _CLIENTTASKLABEL=;
73         %LET _CLIENTPROJECTPATH=;
74         %LET _CLIENTPROJECTNAME=;
75         %LET _SASPROGRAMFILE=;
76         
77         ;*';*";*/;quit;run;
78         ODS _ALL_ CLOSE;
79         
80         
81         QUIT; RUN;
82        

 

 

Solution
‎11-28-2017 05:14 AM
Super User
Posts: 9,548

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

I repeat from a previous post of mine:

You CANNOT, I repeat, CANNOT, use a macro variable with (eg) &acteur00 when you create this macro variable in the same step with call symputx, because

CALL SYMPUTX WORKS WHILE THE DATA STEP RUNS, BUT &ACTEUR00 IS RESOLVED LONG BEFORE THAT WHEN THE DATA STEP IS COMPILED!!!

 

I hope I made myself clear enough now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: data _null_ file with dynamic exit file

Posted in reply to KurtBremser

When I use "%let" there is a problem for SAS .

 

I explain :

 

%let filelink=\\server\underfile1\underfile2\johndoe.xlsx

Now the even link with a space in the name

 

/* option 1 */

%let filelink="\\server\under file1\under file2\johndoe.xlsx"

/* option 2 */

%let filelink='\\server\under file1\under file2\johndoe.xlsx'

In the present case the link own quote, and SAS can't found the link because under Windows there is any quote ...

 

 

 

 

 

 

 

Super User
Posts: 9,548

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

That's why having spaces in file or path names is plain dumb and to be avoided, period.

Remove the root cause, and live happily everafter.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: data _null_ file with dynamic exit file

Posted in reply to KurtBremser

Hello,

After a gret number of hours to test %let and call symput(x) , I have written only %let. To around the issu of call symput(x) which give information only in the second round in this program ...

In this case I use only %let with "call execute" , with this SAS be run the programm in "dynamic way" :

Here is the SAS code :

/* declaration des variables */

%let annee = %sysfunc(date(),year4.);
%let mois = %sysfunc(date(),Month2.);
%let jour = %sysfunc(date(),day2.);
%let lien = '\\batd0\bases_rad\ficovie\' ;
%let general = 'GENERAL';
%let tableori = 'refec9';
%let acteur00 = 'MOA Reglementaire';
%let acteur01 = 'MOE FICOVIE';
%let acteur02 = 'Gestion Pegase';
%let acteur03 = 'Gestion 8X';
%let acteur04 = 'Gestion PTV';
%let acteur05 = 'Gestion Espece';
%let acteur06 = 'Gestion Obseques';
%let acteur07 = 'Gestion BIGBEN';
%let statut = 'Corrige' ;
%let extension = '.xlsx' ;
%let nomtable00 = 'nomtable00' ;
%let nomtable01 = 'nomtable01' ;
%let nomtable02 = 'nomtable02' ;
%let nomtable03 = 'nomtable03' ;
%let nomtable04 = 'nomtable04' ;
%let nomtable05 = 'nomtable05' ;
%let nomtable06 = 'nomtable06' ;
%let nomtable07 = 'nomtable07' ;
%let debprocsql = 'proc sql; create table ' ;
%let mid1procsql = ' as select numero_police, application, flux, cause_evenement, anomalie, date_creation_rejet, acteur , statut , analyse_gestion , analyse_moe, date_derniere_modif, lid, rolper, numper, moesta, attmoesil, dtestrea, notic ' ;
%let mid2procsql = ' from ' ;
%let mid3procsql = ' where acteur like "' ;
%let mid4procsql = '" and statut not like "' ;
%let finprocsql = '" ; quit; ' ;
%let debprocexp = 'proc export data=' ;
%let mid1procexp = ' outfile =' ;
%let guillemet = '"' ;
%let mid2procexp = ' dbms=XLSX replace; ' ;
%let mid3procexp = ' sheet="A_traiter"; ' ;
%let finprocexp = ' run; ' ;

data _null_;

/* proc sql */

morceau1=&debprocsql ;
morceau21=&nomtable00 ;
morceau22=&nomtable01 ;
morceau23=&nomtable02 ;
morceau24=&nomtable03 ;
morceau25=&nomtable04 ;
morceau26=&nomtable05 ;
morceau27=&nomtable06 ;
morceau28=&nomtable07 ;
morceau3=&mid1procsql ;
morceau4=&mid2procsql ;
morceau5=&tableori;
morceau6=&mid3procsql ;
morceau71=&acteur00 ;
morceau72=&acteur01 ;
morceau73=&acteur02 ;
morceau74=&acteur03 ;
morceau75=&acteur04 ;
morceau76=&acteur05 ;
morceau77=&acteur06 ;
morceau78=&acteur07 ;
morceau8=&mid4procsql ;
morceau9=&statut ;
morceau10=&finprocsql ;

call execute (morceau1||morceau21||morceau3||morceau4||morceau5||morceau6||morceau71||morceau8||morceau9||morceau10);
call execute (morceau1||morceau22||morceau3||morceau4||morceau5||morceau6||morceau72||morceau8||morceau9||morceau10);
call execute (morceau1||morceau23||morceau3||morceau4||morceau5||morceau6||morceau73||morceau8||morceau9||morceau10);
call execute (morceau1||morceau24||morceau3||morceau4||morceau5||morceau6||morceau74||morceau8||morceau9||morceau10);
call execute (morceau1||morceau25||morceau3||morceau4||morceau5||morceau6||morceau75||morceau8||morceau9||morceau10);
call execute (morceau1||morceau26||morceau3||morceau4||morceau5||morceau6||morceau76||morceau8||morceau9||morceau10);
call execute (morceau1||morceau27||morceau3||morceau4||morceau5||morceau6||morceau77||morceau8||morceau9||morceau10);
call execute (morceau1||morceau28||morceau3||morceau4||morceau5||morceau6||morceau78||morceau8||morceau9||morceau10);

/* proc export */

morceau201=&debprocexp;
/*morceau202=;*/
morceau203=&mid1procexp;
morceau204=&guillemet;
morceau205=&lien;
morceau206="&annee";
morceau207="&mois";
morceau208="&jour";
morceau209bis=&general;
morceau2010=&extension;
morceau2011=&guillemet;
morceau2012=&mid2procexp;
morceau2013=&mid3procexp;
morceau2014=&finprocexp;

call execute (morceau201||morceau5||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau209bis||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau21||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau71||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau22||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau72||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau23||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau73||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau24||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau74||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau25||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau75||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau26||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau76||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau27||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau77||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);
call execute (morceau201||morceau28||morceau203||morceau204||morceau205||morceau206||morceau207||morceau208||morceau78||morceau2010||morceau2011||morceau2012||morceau2013||morceau2014);

run;



To replace several time proc sql and proc export , there is only 1 query which can be use in dynamic mode ...

Super User
Posts: 9,548

Re: data _null_ file with dynamic exit file

Posted in reply to azertyuiop

Wow. I must say I have never seen someone (here on the communities) making their own life miserable in such a way.

 

What shall this mess accomplish (no code please, just the logic behind it)?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: data _null_ file with dynamic exit file

Posted in reply to KurtBremser

I can't know all technology on a program . I'm not an encyclopedia. I must note information if I found a important information or maybe if you , you give too an information.

 

When I found a solution it's in my habit to share the solution . I think that is minimum when several people help to give a solution. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 303 views
  • 0 likes
  • 3 in conversation