SAS Macro - create a running macro as concatenation of another set of macros

Question

I have a macro related issue that I’m currently struggling to develop and understand. Any pointer on resolving this would be greatly appreciated :-)

It goes something similar below:

I have a ‘n’ [variable] number of macro variables ‘Key’ which resolve to

&Key1=1 &Key=2 &Key3=3 …………….. ………. &Keyn=n

I want to create an automatic running macro ‘Masterkey’ that goes something like

&Masterkey=1 &Masterkey2=12 &Masterkey=123 ………. ……….

i.e. &MasterKeyN=123…..N

How can I get this to work to create ‘&MasterkeyN’ where N is not fixed as can be variable subject to each set of cases with [1-n] keys?

Many thanks.

Nad

Where do the second set of values, the 1, 12, 123 come from? — Reeza, Dec 12 '14 at 14:34
If I follow you correctly, &Masterkey10 = 12345678910 etc. I'm not sure why you're using this naming convection, but be aware that the maximum length of a macro name is 32 characters, so it wouldn't take much to get past this. It may be worthwhile putting how it's going to be used, as a better solution is likely to be available — Longfish, Dec 12 '14 at 14:55
This sounds a lot like a problem in program design rather than something really worth solving. Why do you want to do this? What's the end goal of the program? — Joe, Dec 12 '14 at 15:16

score 1 · Answer 1 · answered Dec 12 '14 at 15:27

I think this probably isn't a useful technique, but I'll answer it anyway.

I'll also assume that &Key1..n may have values other than the number stored in them, and you want those values collected into the &MasterKey1..n variables.

What you'd need to do is use a nested loop, and to know a bit about how macro variables resolve.

%let key1=A;
%let key2=B;
%let key3=C;

%global MasterKey1 MasterKey2 MasterKey3; *so they work outside of the macro;

%macro create_master(numKeys=);
  %do master=1 %to &numKeys;              *Outer loop for the MasterKeys we want to make;
    %let temp=;
    %do keyiter = 1 %to &master;          *Inner loop for the keys that fall into the MasterKey;
      %let temp = &temp.&&Key&keyiter.;   *&& delays macro variable resolution one time.;
    %end;
    %let MasterKey&master.=&temp.;
  %end;
%mend create_master;
%create_master(numkeys=3);

%put &=MasterKey1 &=MasterKey2 &=MasterKey3;

The magic here is &&. Basically, during macro variable parsing, you deal with one or two &s at a time. If it helps put some %put statements inside the loop to see how it works.

To start with, let's jump in towards the end. On this iteration, &temp=AB &Keyiter=3 and &Key3=C.

0. &temp.&&Key&keyiter
1. AB&Key3
2. ABC

So from 0 to 1, the parser sees &temp., the period denoting the end of one variable, so it looks up what is that: &temp.=AB and replaces it with AB. Then it sees two &s, and replaces them with one & but doesn't attempt to resolve anything with them. Then it sees Key, no ampersands there so nothing to do. Then it sees &keyiter, okay, replace that with 3.

Then from 1 to 2, it sees AB, ignores it as it should. Then it sees &Key3 (two ampersands became one don't forget), and now it knows to resolve that to C, which it does - thus ABC.

Note there's nothing 'automatic' about this, because SAS doesn't work that way. You'd need to re-run the macro if it needed to be repopulated (but you could do that at any time basically instantly). — Joe, Dec 12 '14 at 16:07

score 0 · Answer 2 · answered Dec 13 '14 at 15:23

Many thanks to everyone for helpful comments and solutions. Yes, absolutely there can be solutions in SAS in many way. I was probably fixated on approaching that from one angle. Anyway, I've now been able to resolve the issue. Here's a brief summary of the problem at question and 'the solution below:

I have a number of customer and transaction tables. The objective is to match/ join two tables based on match type+process key and matching keys (matching keys are fields in the tables. Instructions on matching is given by table similar to one at the bottom.. I am trying to build a macro that contains the join instructions based on certain matching type and process key, e.g. for matching type=Profile and Process key=3, I want to create a macro that contain a string (link below), which can then be fed into a proc sql command: ]

Table1.Name=Table2.Name 
And Table1.Address=Table2.Address

]

I created a macro for each of the matching key based on match type and process key, and wanted to have a dynamic concatenation of the keys [with ‘and’ text added before the 2nd and subsequent keys]. The problem I'm having is there's no fixed number of matching keys for any given matching type and process key.

Matching_Type Process_Key Matching_Keys 
Profile 1 Name 
Profile 1 Address 
Profile 1 Gender 
Market 1 Name 
Market 1 Income 
Profile 2 Name 
Profile 2 Address 
Profile 2 Gender 
Profile 2 DoB 
Profile 2 Phone_Number 
Market 2 Name 
Market 2 Address 
Market 2 Gender 
Market 2 Income 
Market 2 Transaction_Amount 
Market 2 Credit_Card_Number 
Profile 3 Name 
Profile 3 Address

Solution:

%macro test; 
proc sql noprint; 
select left(put(count(distinct matching_type||left(put(process_key,8.))),8.)) into :num 
from test; 
select distinct matching_type, process_key into :matchkey1 - :matchkey&num, :processkey1-:processkey&num 
from test; 
%do i=1 %to &num; 
%global &&matchkey&i&&processkey&i; 
select 'table1.'||trim(matching_keys)||' = table2.'||trim(matching_keys) into :&&matchkey&i&&processkey&i separated by ' and ' 
from test 
where matching_type="&&matchkey&i" and process_key=&&processkey&i; 
%end; 
quit; 
%mend; 

options mprint; 
%test; 

%put _user_;

Many thanks everyone.

SAS Macro - create a running macro as concatenation of another set of macros

2 Answers2