DATA Step, Macro, Functions and more

%let using a database

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
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,468

Re: %let using a database

Posted in reply to Demographer

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

Posted in reply to Demographer

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,468

Re: %let using a database

Posted in reply to Demographer

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: 111

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,468

Re: %let using a database

Posted in reply to Demographer

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: 111

Re: %let using a database

Exactly what I need. Thank you.

🔒 This topic is solved and locked.

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

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