Hello , Good morning
This code refuse to execute :
data _null_; call symputx("month",month(today())); call symputx("year",year(today())); call symputx("day",day(today())); run; /* %put &month &year &day; export au format xlsx */ %expxlsxdate('\\monserveur\sous dossier\' , &year , &month , &day , 'xlsx' , refec9 , 'mafeuille');
Here is the error message :
WARNING: Apparent invocation of macro EXPXLSXDATE not resolved. SYMBOLGEN: Macro variable YEAR resolves to 2017 SYMBOLGEN: Macro variable MONTH resolves to 11 SYMBOLGEN: Macro variable DAY resolves to 22 24 %expxlsxdate('\\monserveur\sous dossier\' , &year , &month , &day , 'xlsx' , refec9 , 'mafeuille'); _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.
Sas Macro :
%macro expxlsxdate(file , annee , mois , jour , extension , table , nom_feuille); proc export data=work.&table outfile=&file&annee&mois&jour&.'.'extension dbms=&extension replace; sheet=&nom_feuille; run; %mend;
When we must use a macro it's always necessary to use % .
Since when a SAS macro isn't called by " % " symbol but by an other ???
Your seriously making your code as impractical as possible, passing quotes into a macro, your must be mad. This is not the way forward, seriously. For instance, why do you need to pass the quote in? Macro variables only resolve when put between double quotes, so it makes no logical sense at all to specify this.
Secondly, again, why are you passing separate bits in? You have a datastep, use the power of Base SAS to manipulate your data:
data _null_; length fname $200; fname=cats('\\monserveur\sousdossier\',put(today(),yymmdd10.),'.xlsx'); call symput('fname',fname); run; %expxlsdate (fname=&fname.,table=...);
Or just stop trying to create your own strange brand of macro mashed code, proc export is well know, and very simple to type a few characters, you have developed a program which is not only longer, more complicated, but obfuscated as well!
Hello @azertyuiop,
I think you SAS session simply cannot find the macro. Have you tried to execute it altogether and sequentially? as:
%macro expxlsxdate(file , annee , mois , jour , extension , table , nom_feuille);
proc export data=work.&table outfile=&file&annee&mois&jour&.'.'extension dbms=&extension replace;
sheet=&nom_feuille;
run;
%mend;
data _null_;
call symputx("month",month(today()));
call symputx("year",year(today()));
call symputx("day",day(today()));
run;
/* %put &month &year &day; export au format xlsx */
%expxlsxdate('\\monserveur\sous dossier\' , &year , &month , &day , 'xlsx' , refec9 , 'mafeuille');
If this works but your code does not, try to make an include of the macro or put the macro on the right folder so it can be located by your SAS session. Otherwise, please let us know
Hello ,
I have reloaded the include programm with the macro in first time.
%expxlsxdate ()
This macro invocation cannot find any macro called that, so you get the error. Macros need to be defined before the invocation.
Secondly, what is the point of this?
data _null_; call symputx("month",month(today())); call symputx("year",year(today())); call symputx("day",day(today())); run;
You are taking the result of a simple function, putting it into text, then passing the text into a macro, where no doubt you will need to use it further. Call the function when it is needed, don't put repeats into macro variable purely for the sake of a need to use macro. If you absolutely have to pass today():
%expxlsxdate('\\monserveur\sous dossier\' ,month(today()),year(today()),day(today()), 'xlsx' , refec9 , 'mafeuille');
But I would still question why, a simple put date into the correct format would be far better.
Also, if your developing macros, its a good idea to use named parameters, you might know that month should be the second paramter, but someone else may put year there. To be honest though, a macro for this really isn't needed, and if it really has to be done this way, setup a fileref before the invocation and then use the filref:
fileref xlsxfile "\\monserveur\sous dossier\%sysfunc(today(),date9.).xlsx"; %expxlsdate (fref=xlsxfile);
Hello , Good morning ,
I have bursts each field which must pass by this macro like this :
data _null_; call symputx("month",month(today())); call symputx("year",year(today())); call symputx("day",day(today())); call symputx("lien",'\monserveur\sousdossier\'); call symputx("extension",'.xlsx'); call symputx("format",'XLSX'); call symputx("feuille",'mafeuille'); call symputx("quote",'"'); run; %put &month &year &day &lien &extension &format &feuille "e ; /* export au format xlsx */ %expxlsxdate( &lien , &year , &month , &day , &extension , &format , refec9 , &feuille , "e );
SAS log give this code in error :
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 22 23 GOPTIONS ACCESSIBLE; 24 data _null_; 25 call symputx("month",month(today())); 26 call symputx("year",year(today())); 27 call symputx("day",day(today())); 28 call symputx("lien",'\\monserveur\sousdossier\'); 29 call symputx("extension",'.xlsx'); 30 call symputx("format",'XLSX'); 31 call symputx("feuille",'mafeuille'); 32 call symputx("quote",'"'); 33 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 34 35 %put &month &year &day &lien &extension &format &feuille "e ; SYMBOLGEN: Macro variable MONTH resolves to 11 SYMBOLGEN: Macro variable YEAR resolves to 2017 SYMBOLGEN: Macro variable DAY resolves to 22 SYMBOLGEN: Macro variable LIEN resolves to \\monserveur\sousdossier\ SYMBOLGEN: Macro variable EXTENSION resolves to .xlsx SYMBOLGEN: Macro variable FORMAT resolves to XLSX SYMBOLGEN: Macro variable FEUILLE resolves to mafeuille SYMBOLGEN: Macro variable QUOTE resolves to " 36 37 /* export au format xlsx */ 38 39 %expxlsxdate( &lien , &year , &month , &day , &extension , &format , refec9 , &feuille , "e ); SYMBOLGEN: Macro variable LIEN resolves to \\monserveur\sousdossier\ SYMBOLGEN: Macro variable YEAR resolves to 2017 SYMBOLGEN: Macro variable MONTH resolves to 11 2 The SAS System 08:58 Wednesday, November 22, 2017 SYMBOLGEN: Macro variable DAY resolves to 22 SYMBOLGEN: Macro variable EXTENSION resolves to .xlsx SYMBOLGEN: Macro variable FORMAT resolves to XLSX SYMBOLGEN: Macro variable FEUILLE resolves to mafeuille SYMBOLGEN: Macro variable QUOTE resolves to " SYMBOLGEN: Macro variable TABLE resolves to refec9 SYMBOLGEN: Macro variable QUOTE resolves to " SYMBOLGEN: Macro variable FILE resolves to \\monserveur\sousdossier\ SYMBOLGEN: Macro variable ANNEE resolves to 2017 SYMBOLGEN: Macro variable MOIS resolves to 11 SYMBOLGEN: Macro variable JOUR resolves to 22 SYMBOLGEN: Macro variable EXTENSION resolves to .xlsx SYMBOLGEN: Macro variable QUOTE resolves to " SYMBOLGEN: Macro variable FORMAT resolves to XLSX SYMBOLGEN: Macro variable NOM_FEUILLE resolves to mafeuille 40 41 GOPTIONS NOACCESSIBLE; 42 %LET _CLIENTTASKLABEL=; ERROR: Open code statement recursion detected. 43 %LET _CLIENTPROJECTPATH=; ERROR: Open code statement recursion detected. 44 %LET _CLIENTPROJECTNAME=; ERROR: Open code statement recursion detected. 45 %LET _SASPROGRAMFILE=; ERROR: Open code statement recursion detected. 46 47 ;*';*";*/;quit;run; 11 2017 22 \\monserveur\sousdossier\ .xlsx XLSX mafeuille " ;/* export au format xlsx */proc export data=work.refec9 outfile="\\monserveur\sousdossier\20171122.xlsx" dbms=XLSX replace; sheet=mafeuille; run;;GOPTIONS NOACCESSIBLE;;*';*" 48 ODS _ALL_ CLOSE; 49 50 51 QUIT; RUN;
SAS macro is like this now :
%macro expxlsxdate(file , annee , mois , jour , extension , format , table , nom_feuille , quote ); /* trvaux */ proc export data=work.&table outfile="e&file&annee&mois&jour&extension"e dbms=&format replace; sheet=&nom_feuille; run; %mend;
Your seriously making your code as impractical as possible, passing quotes into a macro, your must be mad. This is not the way forward, seriously. For instance, why do you need to pass the quote in? Macro variables only resolve when put between double quotes, so it makes no logical sense at all to specify this.
Secondly, again, why are you passing separate bits in? You have a datastep, use the power of Base SAS to manipulate your data:
data _null_; length fname $200; fname=cats('\\monserveur\sousdossier\',put(today(),yymmdd10.),'.xlsx'); call symput('fname',fname); run; %expxlsdate (fname=&fname.,table=...);
Or just stop trying to create your own strange brand of macro mashed code, proc export is well know, and very simple to type a few characters, you have developed a program which is not only longer, more complicated, but obfuscated as well!
This macro invocation cannot find any macro called that, so you get the error. Macros need to be defined before the invocation.
I have reloaded all macro for that sas consider the changing .
Secondly, what is the point of this?
I burst all data and variable that I have need in the macro .
You are taking the result of a simple function, putting it into text, then passing the text into a macro, where no doubt you will need to use it further. Call the function when it is needed, don't put repeats into macro variable purely for the sake of a need to use macro. If you absolutely have to pass today():
Logicaly it's possible to use a call symput in a data _null_ to stock each value in a variable ? I must send the date of today part way.
But I would still question why, a simple put date into the correct format would be far better.
I don't see the interest to send a date in format "date9" if I want enforce a particular format in the exit ?
"I have reloaded all macro for that sas consider the changing ." - Post the code as it is run (not just a small snippet - the whole thing), with the log. The log is telling you that macro definition is not found.
"I burst all data and variable that I have need in the macro ." - This makes no sense? You are exporting a dataset, it is simple.
"Logicaly it's possible to use a call symput in a data _null_ to stock each value in a variable ? I must send the date of today part way." - Again, this makes not sense.
"I don't see the interest to send a date in format "date9" if I want enforce a particular format in the exit ?" - You misunderstand the point of it. The code which calls the macro is the one that defines the format the macro uses, no need to send parts, you just send one filename string. How that string is formed is up to the calling program.
fname=cats('\\monserveur\sousdossier\',put(today(),date9.),'.xlsx'); Or fname=cats('\\monserveur\sousdossier\',year(today()),month(today()),day(today()),),'.xlsx'); Or any other format or concatenation of strings, it doesn't matter as this is decided by the calling program.
Try to write sentences that make sense. I can understand Worf's Klingon better than your post.
@azertyuiop wrote:
This macro invocation cannot find any macro called that, so you get the error. Macros need to be defined before the invocation.
I have reloaded all macro for that sas consider the changing .
Secondly, what is the point of this?
I burst all data and variable that I have need in the macro .
You are taking the result of a simple function, putting it into text, then passing the text into a macro, where no doubt you will need to use it further. Call the function when it is needed, don't put repeats into macro variable purely for the sake of a need to use macro. If you absolutely have to pass today():
Logicaly it's possible to use a call symput in a data _null_ to stock each value in a variable ? I must send the date of today part way.
But I would still question why, a simple put date into the correct format would be far better.
I don't see the interest to send a date in format "date9" if I want enforce a particular format in the exit ?
Look up CALL EXECUTE so you can avoid creating macro variables or figuring out what you need to pass.
@azertyuiop wrote:
Hello , Good morning
This code refuse to execute :data _null_; call symputx("month",month(today())); call symputx("year",year(today())); call symputx("day",day(today())); run; /* %put &month &year &day; export au format xlsx */ %expxlsxdate('\\monserveur\sous dossier\' , &year , &month , &day , 'xlsx' , refec9 , 'mafeuille');
Here is the error message :WARNING: Apparent invocation of macro EXPXLSXDATE not resolved. SYMBOLGEN: Macro variable YEAR resolves to 2017 SYMBOLGEN: Macro variable MONTH resolves to 11 SYMBOLGEN: Macro variable DAY resolves to 22 24 %expxlsxdate('\\monserveur\sous dossier\' , &year , &month , &day , 'xlsx' , refec9 , 'mafeuille'); _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.Sas Macro :
%macro expxlsxdate(file , annee , mois , jour , extension , table , nom_feuille); proc export data=work.&table outfile=&file&annee&mois&jour&.'.'extension dbms=&extension replace; sheet=&nom_feuille; run; %mend;
When we must use a macro it's always necessary to use % .
Since when a SAS macro isn't called by " % " symbol but by an other ???
Hello ,
Due to an error with a link of file on the network of my company (there is a space in the link ...) it's impossible to export data.
After a correction I can confirm the solution of @Reeza , @Kurt_Bremser , @RW9 and @JuanS_OCS to export data.
Thanks for your help .
Prot-tip: In paths, and filenames, do not use any special characters or spaces. See maxim 44:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.