BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6
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!!
9 REPLIES 9
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
KevinC_
Fluorite | Level 6
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
DanielSantos
Barite | Level 11
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
abdullala
Calcite | Level 5
do not quote macro var assignment.

%let input1=20100121_REFI1001D.xls;

see if this works.
KevinC_
Fluorite | Level 6
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!
KevinC_
Fluorite | Level 6
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
KevinC_
Fluorite | Level 6
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!!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 1021 views
  • 0 likes
  • 4 in conversation