DATA Step, Macro, Functions and more

%let using a database

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

%let using a database

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.

YearsNumber
20063454
200721321
200834324
200943
20104545
20114535
2012435
201345
20143454
201565

How can I do that?

Thanks,


Accepted Solutions
Solution
‎12-29-2012 08:48 AM
PROC Star
Posts: 7,360

Re: %let using a database

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)

View solution in original post


All Replies
Frequent Contributor
Posts: 86

Re: %let using a database

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...

Solution
‎12-29-2012 08:48 AM
PROC Star
Posts: 7,360

Re: %let using a database

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)

Frequent Contributor
Posts: 110

Re: %let using a database

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;

PROC Star
Posts: 7,360

Re: %let using a database

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)

Frequent Contributor
Posts: 110

Re: %let using a database

Exactly what I need. Thank you.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 229 views
  • 7 likes
  • 3 in conversation