How to creat macro pathway?

Accepted Solution Solved
Reply
Super Contributor
Posts: 274
Accepted Solution

How to creat macro pathway?

 Hello:

 

I created an macro pathway so that I don't have copy and paste the pathway everytime when I use proc export.  However I got an error message.  How to fix it?  Thanks.

 

WARNING: Apparent symbolic reference FOLDERTEST1 not resolved.

WARNING: Apparent symbolic reference DT not resolved.

 

data _null_;

dt=put( today( ),date9. );

call symput('dt',dt);

run;

 

%let folder = C:\My Documents\myfiles\;

%let X1=child;

%let X2=mom;

 

DATA TEST&X1;

      SET TEST (keep=id BB dob);

run;

 

DATA TEST&X2;

      SET TEST (keep=record mrn dob);

run;

 

PROC EXPORT DATA= TEST&X1

      outfile= "&folderTEST1&dt..xls "

      dbms=xls replace;

      sheet=&X1;

run;

 

 

PROC EXPORT DATA=TEST&X2

      outfile="&folderTEST1&dt..xls"

      dbms=xls replace;

      sheet=&X2;

run;


Accepted Solutions
Solution
‎06-02-2017 12:33 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: How to creat macro pathway?

Please read me post above.  Particularly the lines:

 

You need to finish the macro dereference with a point:

"&folder.TEST1&dt..xls "

           ^

Note how the dot is after the macro name which is folder, it is not after the word test1 or anywhere else.  Macro references are done as:

&<macro var name>.

Start with an ampersand, finish with a dot.

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: How to creat macro pathway?

You need to finish the macro dereference with a point:

"&folder.TEST1&dt..xls "

Notice it after the word folder.  As a good tip always put the point after the macro name.

 

As a second point, if you have version 9.4 you would have a far simple syntax if you use libname excel.

data a (keep=id bb dob) b (keep=record mrn dob);
  set test;
run;

libname tmp excel "c:\My Documents\myfiles\test%sysfunc(today(),date9.).xlsx";

proc datasets library=work;
  copy a b;
quit; run;

libname tmp clear;
Super Contributor
Posts: 274

Re: How to creat macro pathway?

My SAS is 9.3 version

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: How to creat macro pathway?

Would suggest moving onto 9.4 then, it has far more functionality.

 

Anyways, your question is answered by putting the dot after the macro name.

Super Contributor
Posts: 274

Re: How to creat macro pathway?

My company doesn't pay for the upgrade. Smiley Sad

Grand Advisor
Posts: 10,241

Re: How to creat macro pathway?


ybz12003 wrote:

My company doesn't pay for the upgrade. Smiley Sad


I you have current valid license for 9.3 then the upgrade to 9.4 for the same products should be included.

 

 

Super Contributor
Posts: 274

Re: How to creat macro pathway?

Could you clearify how to put the dot after the macro name?  Thanks.

Grand Advisor
Posts: 10,241

Re: How to creat macro pathway?


ybz12003 wrote:

Could you clearify how to put the dot after the macro name?  Thanks.


%let path = d:\path\;

%put &path.filename.pdq;

Super Contributor
Posts: 274

Re: How to creat macro pathway?

[ Edited ]

I have tried three different versions

 

1.  outfile= "&folderTEST1.&dt..xls " 

2. outfile= "&&folderTEST1&dt..xls " 

3. outfile= "&&folderTEST1.&dt..xls ".

 

18983! &folderTEST1.&dt..xls"

-

22

200

ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBLABEL, DBMS, DEBUG, FILE,

LABEL, OUTFILE, OUTTABLE, REPLACE, TABLE, _DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

 

 

All of the three didn't work.

Solution
‎06-02-2017 12:33 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: How to creat macro pathway?

Please read me post above.  Particularly the lines:

 

You need to finish the macro dereference with a point:

"&folder.TEST1&dt..xls "

           ^

Note how the dot is after the macro name which is folder, it is not after the word test1 or anywhere else.  Macro references are done as:

&<macro var name>.

Start with an ampersand, finish with a dot.

Super Contributor
Posts: 274

Re: How to creat macro pathway?

[ Edited ]

Yes, it works now. 

 

However, I got another problem. 

 

My oringinal datapath is

 

%let folder=\\path_way\ 

 

SAS give me an error messagsage

 

1 ! outfile="\\Test102JUN2017

                  -

                 22

                 76

ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBLABEL, DBMS, DEBUG, FILE,

LABEL, OUTFILE, OUTTABLE, REPLACE, TABLE, _DEBUG_.

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

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: How to creat macro pathway?

I really can't tell from that.  Post all the relevant code or all the relevant log.

Super Contributor
Posts: 274

Re: How to creat macro pathway?

 

 

19018 PROC EXPORT DATA=Test

SYMBOLGEN: Macro variable X2 resolves to mom

19019 outfile="&folder.TN1&dt..xls"

SYMBOLGEN: Macro variable FOLDER resolves to

\\abc.org\locker\data_structure\Test

SYMBOLGEN: Macro variable DT resolves to 02JUN2017

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.

SYMBOLGEN: Macro variable X2 resolves to mom

NOTE: Line generated by the macro variable "DT".

1 "\\abc.org\locker\data_structure\Test\TestTN102JUN2017.xls" dbms=xls

1 ! replace; sheet=child;run;PROC EXPORT DATA=Test

1 ! outfile="\\abc.org\locker\data_structure\Test\TestTN102JUN2017

                  -

                 22

                 76

ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBLABEL, DBMS, DEBUG, FILE,

LABEL, OUTFILE, OUTTABLE, REPLACE, TABLE, _DEBUG_.

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

19020 dbms=xls replace;

19021 sheet=&X2;

19022 run;

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: How to creat macro pathway?

No, there seems to be something else happening there which I can't see in what you have posted.  This line:

NOTE: The SAS System stopped processing this step because of errors.

 

Tells you something went wrong, beforee that proc export error.  Possible an unbalanced quote or something like that.  Maybe post the code (include all the macro assignments.

Super Contributor
Posts: 274

Re: How to creat macro pathway?

option mlogic mprint symbolgen;

 

data _null_;

dt=put( today( ),date9. );

call symput('dt',dt);

run;

 

%let folder = \\abc.org\data_structure\Test;

%let X1=child;

%let X2=mom;

 

DATA &X1;

SET TN (keep=record mrn dob);

run;

DATA &X2;

SET TN (keep=record BB DCG);

run;

 

PROC EXPORT DATA= &X1

outfile= "&folder.TN1&dt..xls"

dbms=xls replace;

sheet=&X1;

run;

 

PROC EXPORT DATA=&X2

outfile="&folder.TN1&dt..xls"

dbms=xls replace;

sheet=&X2;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 17 replies
  • 128 views
  • 1 like
  • 3 in conversation