topic Re: %let Question in SAS Procedures

%let Question

KevinC_ — Thu, 21 Jan 2010 14:49:11 GMT

Hello Everyone,

I use %let to assign a value to a variable. But it's not working as I have hoped.
At the top portion of my program I have:

%let input1='20100121_REFI1001D.xls';

Down below in a proc import I have:

PROC IMPORT out= test_sa_excls_addflds1
datafile= "c:\My Documents\&input1"
dbms=EXCEL REPLACE;
SHEET='LoanListAll (Page 1)';
getnames=yes;
scantext=yes;
mixed=YES;
RUN;

This is the error msg I get:
ERROR: Unable to import, file c:\My Documents\&input1.XLS does not exist.

The Excel doc is stored in c:\My Documents\20100121_REFI1001D.xls. Can anyone tell me what I am doing wrong?
Thank you for any input!!

Re: %let Question

sbb — Thu, 21 Jan 2010 15:07:27 GMT

Why are you coding the single-quote marks in your macro variable?

Suggest you add the following SAS command to increase the diagnostic output so you can see the problem, as generated:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MPRINT;

But I believe that you are not seeing some warning - where the %LET is not assigning the macro variable -- as is explained in the error message.

Scott Barry
SBBWorks, Inc.

Re: %let Question

DanielSantos — Thu, 21 Jan 2010 15:17:43 GMT

Should work, I mean you should get another error, because INPUT1 is in quotes and, I suppose you are trying to import

c:\My Documents\20100121_REFI1001D.xls

and not,

c:\My Documents\'20100121_REFI1001D.xls'

Aside that, INPUT1 should resolve to the value assigned previously.

Is the assignment (%let) enclosed in some macro (%macro ... %mend)?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: %let Question

KevinC_ — Thu, 21 Jan 2010 15:28:12 GMT

Hi sbb, I have switched to double-quote marks in my macro variable and added the diagnostic options. I ran it again now I see the warning in the proc import. Does this mean the %let didn't work? But there was no error or warning in the step where the %let was executed.

248 PROC IMPORT out= test_sa_excls_addflds1
251 datafile= "c:\My Documents\&input1"
WARNING: Apparent symbolic reference INPUT1 not resolved.
252 dbms=EXCEL REPLACE;
253 SHEET='LoanListAll (Page 1)';
254 getnames=yes;
255 scantext=yes;
256 mixed=YES;
257 RUN;

ERROR: Unable to import, file c:\My Documents\&input1.XLS does not exist.

Re: %let Question

sbb — Thu, 21 Jan 2010 16:04:44 GMT

Exactly - review your SAS program and also your expanded SAS log output in the area around the %LET statement - possibly an open comment I would suggest?

Scott Barry
SBBWorks, Inc.

Re: %let Question

abdullala — Thu, 21 Jan 2010 17:52:10 GMT

do not quote macro var assignment.

%let input1=20100121_REFI1001D.xls;

see if this works.

Re: %let Question

KevinC_ — Thu, 21 Jan 2010 18:41:44 GMT

Thank you all for your valuable input!

The problem was the %let was declared inside a RSUBMIT - ENDRSUBMIT. Once I moved it outside of the Rsubmit it worked.

Now I have a new question/problem:

I would like to use macros for the directory as well. The &input1 and &sheet1 macros work nicely. But the directory macro does not work:

libname indir1 'c:\My Documents\';
%let input1=REFI1001D.xls;
%let sheet1=LoanListAll;

PROC IMPORT out= test_sa_excls_addflds1
datafile= "indir1.&input1"
dbms=EXCEL REPLACE;
SHEET=&sheet1;
getnames=yes;
scantext=yes;
mixed=YES;
RUN;

This is what I see in the LOG:

967 datafile= "indir1.&input1"
SYMBOLGEN: Macro variable INPUT1 resolves to REFI1001D.xls
968 dbms=EXCEL REPLACE;
SYMBOLGEN: Macro variable SHEET1 resolves to LoanListAll
969 SHEET=&sheet1;
970 getnames=yes; * variable names are in row 1 ;
971 scantext=yes; * V. 9 only;
972 mixed=YES;
973 RUN;

ERROR: Unable to import, file C:\Documents and Settings\x90100\indir1.REFI1001D.xls does not
exist.

Thank you so much for your help again!

Re: %let Question

KevinC_ — Thu, 21 Jan 2010 18:50:30 GMT

Let me make myself a little more clear. The directory macro is indir1 and it points to c:\My Documents\. But according to the ERROR it seems to point to a different directory.

Re: %let Question

sbb — Thu, 21 Jan 2010 18:50:57 GMT

Your post reads: "...directory macro does not work".

I believe some fundamental learning about SAS macro variables is important here.

You have not declared a SAS %LET statement for INDIR1 which is required. Or your DATAFILE= parameter must reference an explicit file name including complete drive/path/folder/directory information.

The error message is pretty useful here - you have told SAS where to look for an input file (DATAFILE= parameter) and so there was not file by that name, as specified.

Scott Barry
SBBWorks, Inc.

SAS Macro Language: Reference
Macro Variables, Introduction to Macro Variables
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002293823.htm

Re: %let Question

KevinC_ — Thu, 21 Jan 2010 19:09:22 GMT

sbb,

Thank you for reminding me. I have made changes to my code and now it works fine:

%let indir1= c:\My Documents\;
%let input1=REFI1001D.xls;
%let sheet1=LoanListAll;

1043 PROC IMPORT out= test_sa_excls_addflds1
1045 datafile= "&indir1&input1"
SYMBOLGEN: Macro variable INDIR1 resolves to c:\My Documents\
SYMBOLGEN: Macro variable INPUT1 resolves to REFI1001D.xls
1046 dbms=EXCEL REPLACE;
SYMBOLGEN: Macro variable SHEET1 resolves to LoanListAll
1047 SHEET=&sheet1;
1048 getnames=yes;
1049 scantext=yes;
1050 mixed=YES;
1051 RUN;

Thank you again!!