Attaching a variable based on source table in DATA STEP

Question

I am stacking together several data snapshots that exist in separate tables. The following code iterates over a large number of datestamped snapshots and extracts key-value pairs. Is there a simple way to attach the value of the macro variable &InVar. within this DATA STEP?

A simple example might have &channels. with a value of 20140106 20140120 20140127 and &nchannels. with a value of 3.

DATA kv_map;
    SET
        %DO q=1 %TO &nchannels;
        %LET InVar=%SCAN(&channels,&q);
        %PUT &InVar;
            sourcedata.datasnapshot_&InVar.(keep=var_key var_value)
        %END;
    ;
RUN;

The output would then be:

kv_map

╔══════════╦═════════╦════════════╗
║ var_key  ║ var_map ║  provenace ║
╠══════════╬═════════╬════════════╣
║ aaa      ║     123 ║   20140106 ║
║ bbb      ║     432 ║   20140106 ║
║ ccc      ║     313 ║   20140106 ║
║ fff      ║     654 ║   20140120 ║
║ ggg      ║     125 ║   20140120 ║
║ iii      ║     843 ║   20140120 ║
║ jjj      ║     864 ║   20140127 ║
╚══════════╩═════════╩════════════╝

(Table created using http://goo.gl/JIeqZ)

Joe · Accepted Answer · 2014-08-20T15:36:15.137

1

You can write it this way, for any version:

DATA kv_map;
    SET
        %DO q=1 %TO &nchannels;
        %LET InVar=%SCAN(&channels,&q);
        %PUT &InVar;
            sourcedata.datasnapshot_&InVar.(keep=var_key var_value in=_in_&invar.)
        %END;
    ;
    array ins _in_:;
    do _i = 1 to dim(ins);
      if ins[_i] then provenance=vname(ins[_i]);
    end;
RUN;

For SAS 9.3+, there is an easier way.

DATA kv_map;
    SET
        %DO q=1 %TO &nchannels;
        %LET InVar=%SCAN(&channels,&q);
        %PUT &InVar;
            sourcedata.datasnapshot_&InVar.(keep=var_key var_value )
        %END;
        indsname=_provenance 
    ;
    provenance=_provenance; *the indsname variable will not be kept, so you need to assign to a different one;
RUN;

Now, I'd do this a bit differently to make it easier to read. In general if you can move the macro stuff to a separate macro, it simplifies reading code.

%macro source_data(nchannels=,channels=);
              %DO q=1 %TO &nchannels;
            %LET InVar=%SCAN(&channels,&q);
            %PUT &InVar;
                sourcedata.datasnapshot_&InVar.(keep=var_key var_value)
            %END;
%mend source_data;   *and of course you can add another parameter for libname or datasetname if that might vary also;

data kv_map;
  set %source_data(nchannels=3,channels=20140106 20140120 20140127);
run;

The macro can be defined at an earlier point (where other macros might be defined), so as long as you name it logically, it makes your data step code easier to read.

edited Aug 20 '14 at 15:36

answered Aug 20 '14 at 15:16

Joe

62,789
6
49
67

This doesn't seem to work - I think you've injected the assignment in the middle of the `SET` statement - the `;` on the penultimate line is the end of the `SET` statement. – JustinJDavies Aug 20 '14 at 15:21
Oops - you're entirely right. Updated. (This is why I wouldn't do this quite this way, in fact, which i'm updating into the question now). – Joe Aug 20 '14 at 15:22
The new update attaches the final value of `&InVar.` to all records – JustinJDavies Aug 20 '14 at 15:22
Yep, it does doesn't it. Answering questions during training = bad idea. Give me a sec. – Joe Aug 20 '14 at 15:25
Which SAS version are you using? There's a really easy solution if you're on 9.3+. – Joe Aug 20 '14 at 15:26
I think I'm on 9.3, forced to use SAS EG and I forget the SAS version as it isn't on the title bar all the time : ) – JustinJDavies Aug 20 '14 at 15:27
Well, if you can use `indsname` then you're on 9.3+ :) – Joe Aug 20 '14 at 15:30
That's great - I had to move the `indsname=_provenance` to be an option of the `SET` statement rather than a data-set-option. That's a really useful one to know. – JustinJDavies Aug 20 '14 at 15:35
Fixed that in the above. – Joe Aug 20 '14 at 15:36

Attaching a variable based on source table in DATA STEP

1 Answers1