Date:         Thu, 8 Oct 2009 09:12:49 -0400
Reply-To:     Lou <lpogoda@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lou <lpogoda@HOTMAIL.COM>
Organization: A noiseless patient Spider
Subject:      Re: Proc Import with Ampersands in the Filename
Comments: To: sas-l@uga.edu

"Sarah" <sasstuff@hotmail.co.uk> wrote in message
news:24186f0b-1c14-4a5d-94bb-4849ef578b88@l35g2000vba.googlegroups.com...
On Oct 8, 1:26 pm, Hermie <hejac...@gmail.com> wrote:
> > On 8 okt, 14:00, Sarah <sasst...@hotmail.co.uk> wrote:
> >
> >
> >
> >
> >
> > > Hi all,
> >
> > > Thanks in advance for your help. I am trying to import an Excel file
> > > using proc import (I have to use proc import) but the Excel file
> > > contains ampersands in the filename. The file will import ok, but I
> > > cannot get rid of the warnings that are generated as SAS tries to
> > > resolve what it thinks is a macro reference. Unfortunately, this
> > > needs to be sorted out are we are using the SAS generated error
> > > variables to audit and control the process.
> >
> > > My sample code is
> >
> > > proc import out = work.test
> > > datafile = "C:\test_&_a&b_test_mmm_yyyy.xls"
> > > dbms = EXCEL
> > > replace
> > > ;
> > > sheet = "'Sheet1'";
> > > getnames = no;
> > > mixed = yes;
> > > scantext = yes;
> > > usedate = yes;
> > > scantime = yes;
> > > run;
> >
> > > and I have tried and failed with the %superq, %nrbquote, %nrquote,
> > > %nrstr and using single quotes instead of double quotes.
> >
> > > Also I'm using SAS V9.1 by the way.
> >
> > > Many thanks and kind regards,
> >
> > > Sarah
> >
> > at first glanc i should say use single quotes!!
> > names with & between doubles quotes are considerd as macro vars
> >
> > H.- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Hermie,
>
> I thought that would work too!  But as I mentioned, no luck.  I think
> it might be to do with the code that presumably gets generated behind
> the scenes when a proc import is run.
>
> Thanks anyway.
>
> Sarah

You could possibly get around the problem with additional quotes - maybe
single-quote double-quote filename double-quote single-quote, or some other,
more complicated quoting scheme.  But it may be easier to rename the file,
bypassing the ampersands altogether.  Something like

options noxwait noxsync;
data _null_;
   call system('ren c:\test_*_mmm_yyyy.xls test_something_else.xls');
run;

Then import the spreadsheet using the new name.