topic How to creat macro pathway? in SAS Programming

How to creat macro pathway?

ybz12003 — Thu, 01 Jun 2017 14:25:19 GMT

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;

Re: How to creat macro pathway?

RW9 — Thu, 01 Jun 2017 14:37:20 GMT

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;

Re: How to creat macro pathway?

ybz12003 — Thu, 01 Jun 2017 14:47:16 GMT

My SAS is 9.3 version

Re: How to creat macro pathway?

RW9 — Thu, 01 Jun 2017 14:51:53 GMT

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.

Re: How to creat macro pathway?

ybz12003 — Thu, 01 Jun 2017 17:18:11 GMT

My company doesn't pay for the upgrade. 😞

Re: How to creat macro pathway?

ybz12003 — Thu, 01 Jun 2017 17:20:05 GMT

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

Re: How to creat macro pathway?

ballardw — Thu, 01 Jun 2017 17:43:48 GMT

@ybz12003 wrote:

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

%let path = d:\path\;

%put &path.filename.pdq;

Re: How to creat macro pathway?

ballardw — Thu, 01 Jun 2017 17:46:29 GMT

@ybz12003 wrote:

My company doesn't pay for the upgrade. 😞

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

Re: How to creat macro pathway?

ybz12003 — Fri, 02 Jun 2017 13:03:57 GMT

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"

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.

Re: How to creat macro pathway?

RW9 — Fri, 02 Jun 2017 13:02:39 GMT

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.

Re: How to creat macro pathway?

ybz12003 — Fri, 02 Jun 2017 13:13:20 GMT

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

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.

Re: How to creat macro pathway?

RW9 — Fri, 02 Jun 2017 13:17:28 GMT

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

Re: How to creat macro pathway?

ybz12003 — Fri, 02 Jun 2017 13:26:34 GMT

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

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;

Re: How to creat macro pathway?

RW9 — Fri, 02 Jun 2017 13:34:10 GMT

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.

Re: How to creat macro pathway?

ybz12003 — Fri, 02 Jun 2017 13:48:34 GMT

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;

Re: How to creat macro pathway?

RW9 — Fri, 02 Jun 2017 13:56:23 GMT

Sorry, that code, using sashelp.class (and chaning the path) worked fine for me. So either a) you have some things stuck in your session - solution to this would be to exit your session, start fresh and then try, or b) that path you have there - which looks odd to me, it look slike some sort of web address?? - is incorrect or not accessible. Try the fresh session, and try running the code with a local path, e.g. c:\temp\something\...

Once that works then add in any extra bit.

Re: How to creat macro pathway?

ballardw — Fri, 02 Jun 2017 15:56:29 GMT

@ybz12003 wrote:

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;

Assuming that you copied all of that code at one time and pasted it in one step then note that your text highlight colors in the second proc export step are VERY different. The "run" in the first is not highlighted and the "outfile" in the second is the same color as the quoted string in the first. These color differences look suspiciously like there may be some hidden character that SAS is interpretting poorly. If any of the above code involves something copied from a web location and pasted then you may want to delete everything starting after sheet=&X1; and retype from scratch.

Re: How to creat macro pathway?

ybz12003 — Fri, 02 Jun 2017 16:33:06 GMT

Thanks for your help. After I put the whole path including the quotation make, it works fine. However, I could not use call symput for the today macro.