Populate SAS macro-variable using a SQL statement within another SQL statement?

Question

I stumbled upon the following code snippet in which the variable top3 has to be filled from a table have rather than from an array of numbers.

%let top3 = 14 15 42; /* This should be made obsolete.. */
%let no = 3;

proc sql;
   create table want as
   select *
   from (select x, y from foo) a
   %do i = 1 %to &no.;
     %let current = %scan(&top3.,&i.);   /* What do I need to put here? */
     left join (select x, y from bar where z=&current.) row_&current.
     on a.x = row_&current..x
   %end;
   ;
quit;

The table have contains the xs from the string and looks as follows:

I am now wondering how I should modify the %let current = ... line such that current is populated from the table have. I know how to populate a macro variable using proc sql with select .. into, but I am afraid that the way I am going right now is fully against SAS philosophy.

Why is the original query using a %DO loop instead of just doing select x, y from bar where z in (&top3) ? You cannot have more than one variable named Y, did you oversimplify your example? — Tom
– Tom, Commented Dec 9, 2020 at 22:42

Joe · Accepted Answer · 2020-12-09 23:33:45Z

It looks like you're more or less transposing something. If that's the case, this is doable in macro/sql pretty easily.

First, here's the simple version - no macro.

proc sql;
  create table class_t as
  select * from (
    select name from sashelp.class ) class
        left join (
          select name, age as age_Alfred
          from sashelp.class 
          where name='Alfred') Alfred
          on class.name = Alfred.name
  ;
quit;

We grab the value of age from the Alfred row and put it on the main join. This isn't exactly what you're doing, but it seems similar. (I'm just using one table, but you can of course use two here.)

Now, how do we extend this to be table-driven and not handwritten? Macros!

First, here's the macro - just taking the Alfred bit and making it generic.

%macro joiner(name=);
    left join (
          select name, age as age_&name.
          from sashelp.class 
          where name="&name.") &name.
          on class.name = &name..name
%mend joiner;

Second, we look at this and see two things we need to put into macro lists: the SELECT variable list (we'll get one new variable for each call), and the JOIN list.

proc sql;
  select cats('%joiner(name=',name,')')
    into :joinlist separated by ' '
    from sashelp.class;
  select cats(name,'.age_',name)
    into :selectlist separated by ','
    from sashelp.class;   
quit;

And then, we just call it!

proc sql;
  create table class_t as
    select class.name,&selectlist. from (
        select name from sashelp.class) class
        &joinlist.
    ;
quit;

Now, your dataset you call the macro lists from is perhaps the dataset with the 3 rows in it you have above ("have"). The dataset you actually get the appending data from is some other dataset ("bar"), right? And then the ones you join to is perhaps a third dataset ("foo"). Here I just use the one, for simplicity, but the concept is the same, just different sources.

I do not undestand how &selectlist. can be correctly filled with your code. I would expect to be (in the case of only Alfred) be Alfred.age_Alfred and the complete join-piece what is given back by the your macro %joiner(). Do I overlook something here?

Richard · Accepted Answer · 2020-12-10 13:51:39Z

When the lookup data is in a table you can perform a three way join without any need for SAS Macro. You don't provide any data so the example will mock some.

Example:

Suppose a master record has several associated detail records, and the detail records contain a z value used for selection into a result set per a wanted z lookup table.

data masters;
  call streaminit(2020);

  do id = 1 to 100;
    do x = 1 to 100;
      m_rownum + 1;
      code = rand('integer', 10,45);
      output;
    end;
  end;
run;

data details;
  call streaminit(2020);
  do date = 1 to 20;
    do x = 1 to 100;
      do rep = 1 to 5;
        d_rownum + 1;
        amount = rand('integer', 100,200);
        z = rand('integer', 10,45);
        output;
      end;
    end;
  end;
run;

data zs;
input z @@; datalines;
14 15 42
;

proc sql;
  create table want as
  select
    m_rownum
  , d_rownum
  , masters.id
  , masters.x
  , masters.code
  , details.z
  , details.date
  , details.amount
  from
    masters
  left join 
    details
  on
    details.x = masters.x
  inner join
    zs
  on 
    zs.z = details.z
  order by
    masters.id, masters.x, details.z, details.date
  ;
  quit;

Collectives™ on Stack Overflow

Populate SAS macro-variable using a SQL statement within another SQL statement?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related