topic Re: Cannot get my macro variable working in a PROC IMPORT? in SAS Programming

Cannot get my macro variable working in a PROC IMPORT?

LeeJenson — Mon, 01 Mar 2010 14:22:09 GMT

Hi,

I am at a loss as to why my macro variable DATA_FOLDER does not work
with my proc import. I have tried various combinations using a " and a
' defining the variable and in the proc import but any combination I
seem to use does not work. Here I am using single quotes defining the
macro variable and no quotes around the usage of the variable.

6721 %macro test;
6722 %let data_folder = 'c:\';
6723 libname test &data_folder.;
6724 proc import
6725 datafile = &data_folder.'test.xls'
6726 out = test.test1 dbms=excel replace;
6727 run;
6728 %mend test;
6729 %test;
MPRINT(TEST): libname test 'c:\';
NOTE: Libname TEST refers to the same physical library as ACR.
NOTE: Libref TEST was successfully assigned as follows:
Engine: V9
Physical Name: c:\
MPRINT(TEST): proc import datafile = 'c:\''test.xls' out =
test.test1 dbms=excel replace;
MPRINT(TEST): AEXC;
MPRINT(TEST): run;
ERROR: Unable to import, file c:\'test.xls does not exist.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

Re: Cannot get my macro variable working in a PROC IMPORT?

sbb — Mon, 01 Mar 2010 14:31:49 GMT

As revealed in your log, you have stray tick/quotation marks coded. You should not need to use them in your code.

Scott Barry
SBBWorks, Inc.

Re: Cannot get my macro variable working in a PROC IMPORT?

Paige — Mon, 01 Mar 2010 14:32:19 GMT

While you didn't say what operating system you use, I will assume that it is some version of Windows, in which case you wouldn't put c:\ in any quotes whatsoever in macro variable data_folder.

After you fix that, in the PROC IMPORT statement, you again have quotes in the wrong places. It should look like:

proc import datafile="&data_folder\test.xls" ...

You don't want quotes anywhere in the interior of the datefile=" ", you just want quotes at the beginning and end of whatever text string represents your file.

Re: Cannot get my macro variable working in a PROC IMPORT?

LeeJenson — Mon, 01 Mar 2010 14:47:27 GMT

Scott,

I did remove the quotes but them it had a problem with my drive letter!

Lee

Re: Cannot get my macro variable working in a PROC IMPORT?

LeeJenson — Mon, 01 Mar 2010 14:49:50 GMT

Paige,

When I removed quotes it had a problem with e:\ so I am not sure how I pass the drive location to my code?

yes sorry its windows...

Lee

Re: Cannot get my macro variable working in a PROC IMPORT?

Paige — Mon, 01 Mar 2010 15:08:33 GMT

Lee, please show us your code and the SASLOG, and we will be able to help.

Saying "it had a problem" doesn't allow us to provide any help at all.

Re: Cannot get my macro variable working in a PROC IMPORT?

SASPhile — Mon, 01 Mar 2010 15:15:59 GMT

Try this:
%macro test;
%let data_folder = c:\;
libname test "&data_folder.";
proc import
datafile = "&data_folder.test.xls"
out = test.test1 dbms=excel replace;
run;
%mend test;
%test;

Re: Cannot get my macro variable working in a PROC IMPORT?

Paige — Mon, 01 Mar 2010 17:43:52 GMT

Or the slightly more intuitive version

%macro test;
%let data_folder = c:\;
libname test "&data_folder"; /* No period here */
/* Two periods in next line, following a macro variable */
/* resolves to a single period. */
proc import datafile = "&data_folder..test.xls"
out = test.test1 dbms=excel replace;
run;
%mend test;
%test;

Re: Cannot get my macro variable working in a PROC IMPORT?

Cynthia_sas — Mon, 01 Mar 2010 19:14:05 GMT

Hi:
As Paige suggests, seeing your LOG would be most helpful. One suggestion...are you sure that your E drive is available??? For example, review this SAS log:
[pre]
192 %let data_folder = e:\;
193 libname test "&data_folder.";
NOTE: Libref TEST was successfully assigned as follows:
Engine: V9
Physical Name: e:\

194 %let data_folder = j:\;
195 libname test "&data_folder.";
NOTE: Library TEST does not exist.

[/pre]

On my windows system, I DO have an E: drive, but I do NOT have a J: drive.

As a general rule of thumb, it is a bad idea to "pre-quote" your macro variables in the %LET statement. Macro variables are just character strings and quotes belong in the statements where you USE the macro variables.

For example in these two %LET statements below the second (unquoted) value for 16 is the correct usage:
[pre]
%let age = '16';
%let age = 16;
[/pre]

As you can see from this LOG, with quotes inside &AGE, my WHERE statement is incorrect:
[pre]
196 %let age = '16';
197 proc print data=sashelp.class;
198 title "1) Wrong: Students who are &age";
199 where age = &age;
ERROR: WHERE clause operator requires compatible variables.
200 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

201
202 %let age = 16;

203 proc print data=sashelp.class;
204 title "2) Right: Students who are &age";
205 where age = &age;
206 run;

NOTE: There were 1 observations read from the data set SASHELP.CLASS.
WHERE age=16;
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.18 seconds
cpu time 0.03 seconds

[/pre]

This paper is a good introduction to Macro processing concepts.
http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia

Re: Cannot get my macro variable working in a PROC IMPORT?

LeeJenson — Tue, 02 Mar 2010 11:57:50 GMT

Thanks everyone for your input. It is working now. I will include a little log next time I refer to an error (sorry). I am still not 100% what I did wrong as I got myself really confused but its a mixture of putting quotes everywhere, rushing to meet deadlines and going through a big learning curve.

Thank you for your support and I will take time out to read the recommended reading.

:-)

Lee