Defining Fixed SAS Macro Variables

Question

I am trying to have a macro run but I'm not sure if it will resolve since I don't have connection to my database for a little while. I want to know if the macro is written correctly and will resolve the states on each pass through the code (ie do it repetitively and create a table for each state).

The second thing I would like to know is if I can run a macro through a from statement. For example let entpr be the database that I'm pulling from. Would the following resolve correctly:

    proc sql;
    select * from entpr.&state.; /*Do I need the . after &state?*/

The rest of my code:

    libname mdt "........."
    %let state = ny il ar ak mi;

    proc sql;
    create table mdt.&state._members
    as select
corp_ent_cd
,mkt_sgmt_admnstn_cd
,fincl_arngmt_cd
,aca_ind
,prod_type
,cvyr
,cvmo
,sum(1) as mbr_cnt
from mbrship1_&state.
group by 1,2,3,4,5,6,7;
quit;

Answer 1

If &state contains ny il ar ak mi then as it is written, the from statement in your code will resolve to: from mbrship1_ny il ar ak mi - which is invalid SQL syntax.

My guess is that you're wanting to run the SQL statement for each of the following tables:

mbrship1_ny 
mbrship1_il 
mbrship1_ar 
mbrship1_ak 
mbrship1_mi

In which case the simplest macro would look something like this:

%macro do_sql(state=);
  proc sql;
    create table mdt.&state._members
    as select
    ... 
    from mbrship1_&state
    group by 1,2,3,4,5,6,7;
  quit;
%mend;
%do_sql(state=ny);
%do_sql(state=il);
%do_sql(state=ar);
%do_sql(state=ak);
%do_sql(state=mi);

As to your question regarding whether or not to include the . the rule is that if the character following your macro variable is not a-Z, 0-9, or the underscore, then the period is optional. Those characters are the list of valid characters for a macro variable name, so as long as it's not one of those you don't need it as SAS will be able to identify where the name of the macro finishes. Some people always include it, personally I leave it out unless it's required.

Answer 2

When selecting data from multiple tables, whose names themselves contain some data (in your case the state) you can stack the data with:

• UNION ALL in SQL
• SET in Data step

As long as you are stacking data, you should also add a new column to the query selection that tracks the state.

Consider this pattern for stacking in SQL

data one;
do index = 1 to 10; do _n_ = 1 to 2; output; end; end;
run;
data two;
do index = 101 to 110; do _n_ = 1 to 2; output; end; end;
run;

proc sql;
  create table want as
  select 
    source, index
  from 
    (select 'one' as source, * from one)
    union all 
    (select 'two' as source, * from two)
  ;

The pattern can be abstracted into a template for SQL source code that will be generated by macro.

%macro my_ultimate_selector (out=, inlib=, prefix= states=);
  %local index n state;
  %let n = %sysfunc(countw(&states));

  proc sql;
    create table &out as
    select 
      state
      , corp_ent_cd
      , mkt_sgmt_admnstn_cd
      , fincl_arngmt_cd
      , aca_ind
      , prod_type
      , cvyr
      , cvmo
      , count(*) as state_7dim_level_cnt
    from

      %* ----- use the UNION ALL pattern for stacking data -----;

      %do index = 1 %to &n;
        %let state = %scan(&states, &index);

        %if &index > 1 %then %str(UNION ALL);

        (select "&state" as state, * from &inlib..&prefix.&state.)

      %end;

    group by 1,2,3,4,5,6,7,8  %* this seems to be to much grouping ?;
    ;
  quit;
%mend;

%my_ultimate_selector (out=work.want, inlib=mdt, prefix=mbrship1_, states=ny il ar ak mi)

If the columns of the inlib tables are not identical with regard to column order and type, use a UNION ALL CORRESPONDING to have the SQL procedure line up the columns for you.