Date:         Tue, 25 Nov 2008 13:27:41 -0600
Reply-To:     Herbert Morley A <Morley.Herbert@HCAHEALTHCARE.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Herbert Morley A <Morley.Herbert@HCAHEALTHCARE.COM>
Subject:      Macro won't resolve variables
Content-Type: text/plain; charset="us-ascii"

I need help- SAS Macros drive me nuts! I'm impressed with the gurus on this list who write huge complex macros that work. Despite all my days of programming (from Fortran days on), SAS macros still bewilder me.

I have  a group of Excel spreadsheets each containing data for a year with one tab per month. I am trying to read them in with a Macro so that I don't have to write 50-70 filename statements. Even though its archaic, DDE works for the purpose and I use it regularly. This macro works fine when I enter the macro values in by hand. But it only seems to recognize outfil as a macro variable (see Log below). What have I done wrong.

Thanks in advance
Morley Herbert

%MACRO VASC(yr1, moyr, outfil);
FILENAME p2 DDE 'Excel|H:\PICC Team\[Vasc&yr1..xls]&moyr.!R2C1:R1000C12' NOTAB  ;

DATA &outfil  ;
LENGTH patient $30   ScanDx $50 ;
INFILE  p2   MISSOVER DLM ='09'x  TRUNCOVER DSD LRECL=2154 ;
INFORMAT  ScanDate  mmddyy10.  ;
INPUT
Seq
ScanDate
Tech    : $
Patient : $
PatID
SexAge : $
Rm : $
Tape : $
arm_vein : $
PHYS : $
Indic : $
ScanDx : $
;
RUN;

%MEND;


%VASC(2005, Jan 05, Jan05);
QUIT;
----------------------------------------------------------------
LOG FILE

SYMBOLGEN:  Macro variable OUTFIL resolves to Jan05
WARNING: END-OF-RECORD option TRUNCOVER in effect for infile P2.

ERROR: Physical file does not exist, Excel|H:\PICC Team\[Vasc&yr1..xls]&moyr.!R2C1:R1000C12.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.JAN05 may be incomplete.  When this step was stopped there were 0
         observations and 12 variables.
WARNING: Data set WORK.JAN05 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           5.53 seconds
      cpu time            0.01 seconds