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
"Sarah" <email@example.com> wrote in message
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.
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;
call system('ren c:\test_*_mmm_yyyy.xls test_something_else.xls');
Then import the spreadsheet using the new name.