CODEWITHSUNDEEP
sasdatastep
JustinJDavies
JustinJDavies

Reputation: 2693

Attaching a variable based on source table in DATA STEP

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)

Upvotes: 1

Views: 81

Answers (1)

Joe
Joe

Reputation: 63424

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.

Upvotes: 1

Related Questions