How can i assign macro variable value from a table and append data of a macro output in SAS

0
votes

I have table with 10 values to be used as input one by one in the macro. Below is the structure and table name

Table b
Column 1 Column 2
P_0          10
P_10         34
P_20         55
P_30         67

I need to rerun the following code with the above values and append the data

proc sql;
create table a1 as
select * from table a
where amount>=p_0;

proc sql;
create table a2 as
select * from table a
where amount>=p_10;
......

How can I define the macro variable and write a macro for the same in SAS

2
sassas-macro
Can you clarify what you want? Your sample code references a table A you didn't define. Your example table B is not used (and it has variable names with embedded spaces). – Tom
should a1 be for when a.amount > 10, a2 for when amount > 34, etc... – Richard

2 Answers

1
votes

You don't need a macro - use call execute:

data _null_;
  set b;
  if _n_=1 then call execute('proc sql;');
  str1=cats('create table a',_n_);
  retain str2 ' as select * from table a where amount>=';
  call execute(str1!!str2!!'column 1'n!!";');
run;

this will generate the below in a call stack and run the lot after the data step:

proc sql;
create table a1 as select * from table a where amount>= P_0;
create table a2 as select * from table a where amount>= P_10;
create table a3 as select * from table a where amount>= P_20;
create table a4 as select * from table a where amount>= P_30;
0
votes

The SQL INTO clause will assign data set variable values to macro variable values. 

data have;
  do acctid = 1 to 10;
    do _n_ = 1 to 200;
      txid + 1;
      amount = floor(100*ranuni(123))-25;
      array v(10) (1:10);
      output;
    end;
  end;
run;

data params; input name $ value;datalines;
P_0          10
P_10         34
P_20         55
P_30         67
run;

%macro foo;

  proc sql noprint;
    select name, value into :name1-, :value1-
    from params;

  %local N i;
  %let N = &SQLOBS;

  data
    %do i = 1 %to &N;

    want&i

    %end;
  ;

    set have;

    * complicated stuff involving v1-v10;

    %do i = 1 %to &N;

    if amount >= &&value&i then do;
      splitfor = "&&name&i";
      OUTPUT want&i;
    end;

    %end;

  run;

%mend;

options mprint;

%foo;

Due to macro being used for source code generation this technique should not be used when high precision comparisons are needed.