Desktop productivity for business analysts and programmers

parameters refused macro

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

parameters refused macro

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 ???


Accepted Solutions
Solution
4 weeks ago
Super User
Super User
Posts: 8,190

Re: parameters refused macro

Posted in reply to azertyuiop

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!

View solution in original post


All Replies
Trusted Advisor
Posts: 1,436

Re: parameters refused macro

[ Edited ]
Posted in reply to azertyuiop

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

Contributor
Posts: 56

Re: parameters refused macro

Posted in reply to JuanS_OCS

Hello ,

 

I have reloaded the include programm with the macro in first time. 

Super User
Super User
Posts: 8,190

Re: parameters refused macro

Posted in reply to azertyuiop
%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);

 

Contributor
Posts: 56

Re: parameters refused macro

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 &quote ;

/*  export au format xlsx */

%expxlsxdate( &lien , &year , &month , &day , &extension , &format , refec9 , &feuille , &quote );

 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 &quote ;
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 , &quote );
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=&quote&file&annee&mois&jour&extension&quote dbms=&format replace;
sheet=&nom_feuille;
run;

%mend;

 

Solution
4 weeks ago
Super User
Super User
Posts: 8,190

Re: parameters refused macro

Posted in reply to azertyuiop

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!

Contributor
Posts: 56

Re: parameters refused macro

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 ?

 

 

 

 

Super User
Super User
Posts: 8,190

Re: parameters refused macro

Posted in reply to azertyuiop

"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.

Super User
Posts: 8,080

Re: parameters refused macro

[ Edited ]
Posted in reply to azertyuiop

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 ?

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 20,252

Re: parameters refused macro

Posted in reply to azertyuiop

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 ???




 

 

Contributor
Posts: 56

Re: parameters refused macro

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 , @KurtBremser , @RW9 and @JuanS_OCS to export data.

 

Thanks for your help .Man Very Happy

Super User
Super User
Posts: 8,190

Re: parameters refused macro

Posted in reply to azertyuiop

Prot-tip: In paths, and filenames, do not use any special characters or spaces.  See maxim 44:

https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers...

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 246 views
  • 10 likes
  • 5 in conversation