BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
azertyuiop
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

11 REPLIES 11
JuanS_OCS
Amethyst | Level 16

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

azertyuiop
Quartz | Level 8

Hello ,

 

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26
%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);

 

azertyuiop
Quartz | Level 8

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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!

azertyuiop
Quartz | Level 8

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 ?

 

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Kurt_Bremser
Super User

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 ?

 

Reeza
Super User

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




 

 

azertyuiop
Quartz | Level 8

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 .Man Very Happy

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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