Date:         Mon, 13 Feb 2012 16:26:35 -0500
Reply-To:     Tom Abernathy <tom.abernathy@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Tom Abernathy <tom.abernathy@GMAIL.COM>
Subject:      Re: Why didn't the macro work

Why do you think it didn't work? They both took the same 0.03 seconds on CPU
time.

Had you already created the output macro variables (HospList2005 for
example) before calling the macro?  If not then it made a local macro
variable that then disappeared when the macro ended.
You could add the statement
  %global HospList&yr;
to the top of you macro.



On Mon, 13 Feb 2012 16:15:23 -0500, Kirby, Ted <ted.kirby@LEWIN.COM> wrote:

>I just created a program with the following code:
>
>%macro GetProvLists;
> proc sql noprint;
> %do yr= 2005 %to 2008;
>  select distinct byte(34)||Provider_Number||byte(34) into :HospList&yr.
separated by " "
>  from out.Final&yr.Provs;
> %end;
> quit;
>%mend /* GetProvLists */;
>%GetProvLists;
>
>However, it did not work.  The macro variables HospList2005-HospList2008
were not created.  However, when I "hard-wired" the values for the macro
variable "yr" into the code, it worked
>perfectly as in:
>
>proc sql noprint;
>select distinct byte(34)||Provider_Number||byte(34) into :HospList2005
separated by " "
>from out.Final2005Provs;
>select distinct byte(34)||Provider_Number||byte(34) into :HospList2006
separated by " "
>from out.Final2006Provs;
>select distinct byte(34)||Provider_Number||byte(34) into :HospList2007
separated by " "
>from out.Final2007Provs;
>select distinct byte(34)||Provider_Number||byte(34) into :HospList2008
separated by " "
>from out.Final2008Provs;
>quit;
>
>Here is the Log for the two code executions:
>
>55   %macro GetProvLists;
>56       proc sql noprint;
>57       %do yr= 2005 %to 2008;
>58           select distinct byte(34)||Provider_Number||byte(34) into
:HospList&yr. separated by " "
>59           from out.Final&yr.Provs;
>60       %end;
>61       quit;
>62   %mend /* GetProvLists */;
>63   %GetProvLists;
>MPRINT(GETPROVLISTS):   proc sql noprint;
>MPRINT(GETPROVLISTS):   select distinct byte(34)||Provider_Number||byte(34)
into :HospList2005
>separated by " " from out.Final2005Provs;
>MPRINT(GETPROVLISTS):   select distinct byte(34)||Provider_Number||byte(34)
into :HospList2006
>separated by " " from out.Final2006Provs;
>MPRINT(GETPROVLISTS):   select distinct byte(34)||Provider_Number||byte(34)
into :HospList2007
>separated by " " from out.Final2007Provs;
>MPRINT(GETPROVLISTS):   select distinct byte(34)||Provider_Number||byte(34)
into :HospList2008
>separated by " " from out.Final2008Provs;
>MPRINT(GETPROVLISTS):   quit;
>NOTE: PROCEDURE SQL used (Total process time):
>      real time           0.20 seconds
>      cpu time            0.03 seconds
>
>64
>65   /*  For some reason the macro did not work, so executed the following
code,
>66       "hard-wiring" the values from the macro variable "yr" into PROC
SQL.    */
>67   proc sql noprint;
>68   select distinct byte(34)||Provider_Number||byte(34) into :HospList2005
separated by " "
>69   from out.Final2005Provs;
>70
>71   select distinct byte(34)||Provider_Number||byte(34) into :HospList2006
separated by " "
>72   from out.Final2006Provs;
>73
>74   select distinct byte(34)||Provider_Number||byte(34) into :HospList2007
separated by " "
>75   from out.Final2007Provs;
>76
>77   select distinct byte(34)||Provider_Number||byte(34) into :HospList2008
separated by " "
>78   from out.Final2008Provs;
>79   quit;
>NOTE: PROCEDURE SQL used (Total process time):
>      real time           0.15 seconds
>      cpu time            0.03 seconds
>
>Why did the macro take longer to not do anything than the "hard-wired" code
took to create the variable lists that I wanted?
>
>Thanks for any help you can give so that I can understand what happened.
>
>************* IMPORTANT - PLEASE READ ********************
>
>This e-mail, including attachments, may include confidential and/or
proprietary information,
>and may be used only by the person or entity to which it is addressed.  If
the reader of this
>e-mail is not the intended recipient or his or her authorized agent, the
reader is hereby
>notified that any dissemination, distribution or copying of this e-mail is
prohibited. If you
>have received this e-mail in error, please notify the sender by replying to
this message
>and delete this e-mail immediately.
>