%let using a database

[Demographer]

Hi,

I have a macro who looks like this:

%macro macr(years);

%let number=500;

...many more lines...

%mend;

Instead of "500", I want to assign to %let number a value from a database in which the first column corresponds to the parameter of the macro, i.e.

Years	Number
2006	3454
2007	21321
2008	34324
2009	43
2010	4545
2011	4535
2012	435
2013	45
2014	3454
2015	65

How can I do that?

Thanks,

[bnarang]

Hi Demographer

Do you mean for each year as macro variable, the corrosponding Number as macro variabe value.

If your answer is yes, then you can do like this:

data _null_;

Set Temp;

  call symput("x"||years, number);

run;

%put &x2006, &x2007 etc...

[art297 · Accepted solution]

Are you asking how to accomplish something like the following?:

/*existing database*/

data XWalk;

  input Years Number;

  cards;

2006 3454

2007 21321

2008 34324

2009 43

2010 4545

2011 4535

2012 435

2013 45

2014 3454

2015 65

;

%macro macr(years);

  proc sql noprint;

    select number into :number

      from XWalk

        where years=&years.

    ;

  quit;

/*test*/

  %put %eval(5*&number);

/*...many more lines...*/

%mend;

%macr(2010)

[Demographer]

Thanks, it works.

If I have more than one column, is there an easier way than doing this?

proc sql noprint;

    select column1 into :column1

    from temp

    where year=&year.;

    select column2 into :column2

    from temp

    where year=&year.;

    select column3 into :column3

    from temp

    where year=&year.;

quit;

[art297]

Again, if I correctly understand what you are trying to do, how about?:

/*existing database*/

data XWalk;

  input Years column1 column2;

  cards;

2006 3454 1

2007 21321 2

2008 34324 3

2009 43 4

2010 4545 5

2011 4535 6

2012 435 7

2013 45 8

2014 3454 9

2015 65 10

;

%macro macr(years);

  proc sql noprint;

    select column1,column2

      into :column1, :column2

        from XWalk

          where years=&years.

    ;

  quit;

/*test*/

  %put %eval(5*&column1);

  %put %eval(5*&column2);

/*...many more lines...*/

%mend;

%macr(2010)

[Demographer]

Exactly what I need. Thank you.